cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 12 Month Rolling Average (Fiscal Calendar) Calculation

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!

3 REPLIES 3
Anonymous
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!

Super User

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

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

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors