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

## Struggling with Rolling 12 Sum Dax

There are about five topics on this forum that addresses Rolling/Training Sums. I've read them all and attempted to apply them to my applications, however; seem to have an issue still.

I have 3 measures within my table.

1. Total Terms | Total Terms = CALCULATE(SUM('Turnover Matrix '[Total Terminations]))

3. % of Terms | % of Termination = DIVIDE([Total Terms],[Avg. Headcount]," ")

All checks out, calculates correctly when I plot and verfied in a table.

I'm now trying to write a DAX to calculate the rolling 12 months % of Terms based on a date filter. Any suggestion?

Here's a screenshot of what the data looks like in Excel. I'm trying to get to that 67 total %. Is it possible to do this with the above measures in place?

Dax Formula Currently:

``````Rolling 12M Turnover =
VAR __NrOfRollingMonths = 12
VAR __CurrentDate = MAX ( 'Dates'[Date])
VAR __RollingMonths =
FILTER (
ALL ( 'Dates' ),
'Dates'[Date] > EDATE ( __CurrentDate, - __NrOfRollingMonths )
&& 'Dates'[Date] <= __CurrentDate
)
RETURN
CALCULATE ( [% of Termination], __RollingMonths )``````

The results are not accurate lol. It appears that it's giving me the overal % for the entire date selection.
1 ACCEPTED SOLUTION
Super User

Try using a date calendar like this

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

6 REPLIES 6
Super User

Try using a date calendar like this

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Anonymous
Not applicable

Hey @amitchandak I would love to use that DAX, actually, I tried to use it. Unfortunately, I'm calculating the % of Churn using a measure, and following your dax, doesn't allow me to calculate rolling 12 month churn with a measure.

Is there another option to do this with measures? Or do I need to build a table and have the churn calculated row by row by date?

Also, I do have a dedicated Dates[Date] calendar setup and it's connected to my tables, finally it's set as my main date table within my modle.

Super User

@Anonymous , Can you share sample data and sample output.

Anonymous
Not applicable

Yes. You can download the dataset I'm working with here. One sheet contains the dataset, the other contains the desired outputs and visualization.

Super User

@Anonymous ,I can see many calculations, not how got Total Terms And Average HC

Anonymous
Not applicable

@amitchandak  Average HC is based on the average of start of month HC and end of month HC. 🙂 Total terms, is based on the number of leavers in that month.