Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
How would I work out the moving annual trend for quarterly data, given a set of conditions:
i.e.
sum 4 consecutive quarters ( date on this row and previous 3 quarters) of Value A for category A given category B given category C
divided by sum of
sum 4 consecutive quarters ( date on this row and previous 3 quarters) of Value B for category A given category B given category C
In excel I normally do a VLOOKUP for the quarters and create an 'index' number. Then use sumifs so that I have my categories and index with their conditions then sum for index, index-1, index-2 and index -3
i.e. sum(sumifs( testscores, year group, name of year group, class, class name, index, current date), sumifs( testscores, year group, name of year group, class, class name, index, current date-1), sumifs( testscores, year group, name of year group, class, class name, index, current date-2), sumifs( testscores, year group, name of year group, class, class name, index, current date-3)
(and divide by a different value with same conditions if need be)
Solved! Go to Solution.
In this scenario, I suggest you use a full calendar table to limit the Previous 3 Quarter context filter. I assume you those Category A, B, C columns and Value A, B columns are in same table. Then you can create a measure like below:
[3 Quarter Moving Sum Value A] = CALCULATE ( SUM ( Table[ValueA] ), DATESINPERIOD ( Calendar[Date], LASTDATE ( Calendar[Date] ), -3, QUARTER ), FILTER ( ALL ( Table ), Table[Category A] = "Category A" && Table[Category B] = "Category B" && Table[Category C] = "Category C" ) )
Regards,
In this scenario, I suggest you use a full calendar table to limit the Previous 3 Quarter context filter. I assume you those Category A, B, C columns and Value A, B columns are in same table. Then you can create a measure like below:
[3 Quarter Moving Sum Value A] = CALCULATE ( SUM ( Table[ValueA] ), DATESINPERIOD ( Calendar[Date], LASTDATE ( Calendar[Date] ), -3, QUARTER ), FILTER ( ALL ( Table ), Table[Category A] = "Category A" && Table[Category B] = "Category B" && Table[Category C] = "Category C" ) )
Regards,
Hi Mat
The pattern you should consider is the Moving Average pattern from the link below. I use it regularly and it works a treat. Make sure you have a Date table and adjust the table/column names to suit.
http://www.daxpatterns.com/statistical-patterns/
Cheers,
Phil
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.