cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Patricc
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

12326.11.20191
12328.12.20191
12315.9.20201
12321.9.20201
12327.1.20211
12315.2.20211
12323.2.20211
4565.2.20191
4566.7.20191
4567.7.20191
4567.8.20191
45629.9.20191
45626.11.20191
45628.12.20191
45628.7.20201
4563.9.20201
45615.9.20201
45621.9.20201
4561.10.20201
45615.10.20201
4561.12.20201
45624.1.20211
45627.1.20211
4567.2.20211
45615.2.20211
45623.2.20211
45626.2.20211
4566.3.20211
45615.6.20211

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/Q12019/Q22019/Q32019/Q42020/Q12020/Q22020/Q32020/Q42021/Q12021/Q2
123   2  2 3 
4561 42  4371

 

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

10 REPLIES 10
Patricc
Regular Visitor

v-stephen-msft
Community Support
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.

Problem has not been solved.

Samarth_18
Solution Sage
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:-

Samarth_18_0-1627284227945.png

Thanks,

Samarth

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.

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.

Samarth_18_1-1627302090747.png

 

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

 

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.

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:-

Samarth_18_0-1627476259525.png

 

Hope it will helps you. 🙂

 

Thanks,

Samarth

Patricc_0-1628850687673.png

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

@Patricc ,

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.