cancel
Showing results for
Did you mean:
Regular Visitor

## Grouping data into quarters and then comparing each quarter's data.

So I have about 7000 lines of data. The data describes the dates of damage for spare parts. The data has approx. 3000 different part numbers. Each line has a part number, date of damage, and quantity. Here is an example of the data that I would like to modify:

Part Date Quantity

 123 26.11.2019 1 123 28.12.2019 1 123 15.9.2020 1 123 21.9.2020 1 123 27.1.2021 1 123 15.2.2021 1 123 23.2.2021 1 456 5.2.2019 1 456 6.7.2019 1 456 7.7.2019 1 456 7.8.2019 1 456 29.9.2019 1 456 26.11.2019 1 456 28.12.2019 1 456 28.7.2020 1 456 3.9.2020 1 456 15.9.2020 1 456 21.9.2020 1 456 1.10.2020 1 456 15.10.2020 1 456 1.12.2020 1 456 24.1.2021 1 456 27.1.2021 1 456 7.2.2021 1 456 15.2.2021 1 456 23.2.2021 1 456 26.2.2021 1 456 6.3.2021 1 456 15.6.2021 1

What I would like to do, is create a visualisation (probably just a table) that would in a way alarm of the parts whose damage rate is on the rise. I was thinking of dividing the dates into quarters, and I already have a date table for that. The tricky part is dividing the data into quarters in a way which would allow me to then make a comparison formula for each part's each quarter. I am not yet sure what the formula would be, but it would work best if it used the quantities from previous quarters and then gave a value based how the current quarters quantity compares to the previous ones.

So I suppose I should have to get them first like this, and then be able to create a formula in between the quarter columns.

 2019/Q1 2019/Q2 2019/Q3 2019/Q4 2020/Q1 2020/Q2 2020/Q3 2020/Q4 2021/Q1 2021/Q2 123 2 2 3 456 1 4 2 4 3 7 1

Or if there is a better way, I am more than happy to hear it.

10 REPLIES 10
Regular Visitor
Community Support

Hi @Patricc ,

Could you tell me if your problem has been solved?

If it is, kindly Accept the helpful reply as the solution. More people will benefit from it.

Or you are still confused about it, please tell us what expected results you want. Whant kind of formula do you want to create?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regular Visitor

Problem has not been solved.

Solution Sage

Hi @Patricc

You can use matrix visual  and it will serve your purpose without any code. What you can do is, put your "Part Date Quatity" column in row,dates in column and keep only Year,Quater from date hierarchy and put Quantity column in values section.

PFB screenshot for reference:-

Thanks,

Samarth

Regular Visitor

Hello @Samarth_18 . Thank you for your reply and help. From this, how can I make the comparisons between the quarters from this one, since this only seems to be a visual. Can I somehow make this into a table and then add the comparisons that I want there?

As I said, I would need to be able to compare the amounts with some sort of formula in order to be able to identify the parts whose damage rate is on the rise.

Solution Sage

Hi @Patricc

You can create a column for the quater in your table.

``Quater = QUARTER('Table (5)'[date])``

Now create a two measure for current and previous quarter

``current_quarter = CALCULATE(SUM('Table (5)'[quantity]),FILTER(ALLSELECTED('Table (5)'), 'Table (5)'[Quater] =MAX('Table (5)'[Quater])))``
``Previous_Quarter = CALCULATE(SUM('Table (5)'[quantity]),FILTER(ALLSELECTED('Table (5)'), 'Table (5)'[Quater] =MAX('Table (5)'[Quater])-1))``

And add it into table like below.

I have kept current and previous quarter calculation seperately so that you can use it for your further calculation according to your need.

You can simple take difference of these two measure to check how much change happen between these two quater.

``Change = [current_quarter] - [Previous_Quarter]``

Thanks,

Samarth

Regular Visitor

Thanks a ton for your effort @Samarth_18 but as you can see this doesn't provide a solution, since the measures calculate all of the items within that quarter and not the individual parts. So in the example the parts 123 and 456 both have the same values for quarters, because the value is a sum of both of them. It also doesn't take the year into account.

Solution Sage

Hi @Patricc

Sorry my bad, i should have test it more thoroughly.

You can try below steps to get your expected output.

1. As you have mentioned you have date table and it must connected with data table so you can add dates from your date table and required column from data table.

2. Now for getting current quarter quantity you can either directly add your quantity column or you can add measure seperately with below code:-

``current_quarter = CALCULATE(SUM('Table (5)'[quantity]))``

3. For getting quantity for previous quarter you can create a measure with below code:-

``Previous_Quarter = CALCULATE(SUM('Table (5)'[quantity]),PREVIOUSQUARTER(CALENDER_TABLE[Date]))``

Final output:-

Hope it will helps you. 🙂

Thanks,

Samarth

Regular Visitor

This is what it looks like, the previous_quarter adds another row for each part, and shows the next quarter and its previous quarter.

Solution Sage

Is it possible for you to share your PBIX file. You can delete your sensitive data and only keep required data.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!