12 Month Rolling Average (Fiscal Calendar) Calculation

[Anonymous]

Hi,

I'm trying to recreate the following formula from Excel in DAX:

The 12 month rolling avergae formula is: 

=SUM(E81:E133)/SUM(D81:D133)*100000

=SUM(E82:E134)/SUM(D82:D134)*100000

=SUM(E83:E135)/SUM(D83:D135)*100000

Column E is Total X

Column D is Total Y

So it's basically summing the last 53 rows of the 'Total X' column divided by last 53 rows of 'Total Y' column * 100000.

I'm using the company calendar which is fiscal periods so cannot use any of the inbuilt date/time intelligence functions or the quick measures. My calendar looks like this:

Please could someone explain how I can create the same formula in dax...It's driving me mad!

[Anonymous]

@amitchandak thank you for this, my figure is still not quite matching up with the excel spreadsheet. I think the fact the column is called '12 month AFR rolling average' might be a bit confusing because the calculation is actually using the last 53 weeks. 

So for example the 12 month rolling average for W/C 17/01/2021 is 16.3 which is calculated by adding the data starting from W/C 19/01/2020 to W/C 17/01/2021 (53 weeks)

When using your first measure the figure I get is 17.0 for W/C 17/01/2021 and the second measure gives 17.4

Thank you for your help with this!

[amitchandak]

@Anonymous , you do have week option in period, check or you can use 364 days etc 

or you can have column like

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

use WEEKDAY('Date'[Date],1) for sunday week

Last 53 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-53 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

[amitchandak]

@Anonymous , Assuming month are not standard, Create a Rank column in date table on period start or yyyypp

Period Rank = RANKX(all('Period'),'Period'[year period],,ASC,Dense)

Example rolling measure 

rolling = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Period Rank]>=max('Date'[Period Rank])-12 && 'Date'[Period Rank]<=max('Date'[Period Rank])) )

or

rolling = CALCULATE(Averagex(Values('Date'[Year period]) ,calculate(sum('order'[Qty]))),filter(ALL('Date'),'Date'[Period Rank]>=max('Date'[Period Rank])-12 && 'Date'[Period Rank]<=max('Date'[Period Rank])) )

This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1))