Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Not applicable

12 Month Rolling Average (Fiscal Calendar) Calculation



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


The 12 month rolling avergae formula is: 






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!

Not applicable

@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!

@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)
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])))

Super User
Super User

@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])) )




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

Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Kudoed Authors