Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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!
@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)
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])))
@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))
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
19 | |
11 | |
10 | |
8 | |
8 |
User | Count |
---|---|
20 | |
13 | |
8 | |
7 | |
6 |