Let's say I have a table that stores quarterly sales data. How would I construct a DAX formula to get a rolling average of last 5 years on quarter by quarter basis? My below formula seems to only give results for the last 5 years for ALL quarters. What I want
is the average of Q1 this year, Q1 last year, Q1 2 years ago, and so on until 5 years ago from current row.
5 Year Average of Quarterly Changes:=IF(HASONEVALUE(DimDate[DateKey]), CALCULATE( AVERAGEX( DATESINPERIOD(DimDate[DateKey], DATEADD(DimDate[DateKey], -5, YEAR), 5, YEAR), [Quarter on Quarter Change] )))
Edited bykostazTuesday, September 10, 2013 7:32 PM
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.