Struggling with Rolling 12 Sum Dax

[Anonymous]

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

2. Avg. Headcount | Avg. Headcount = CALCULATE(SUM('Turnover Matrix '[Average Headcount]))

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. 

[amitchandak · Accepted solution]

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://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Anonymous]

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. 

[amitchandak]

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

[Anonymous]

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

[amitchandak]

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

[Anonymous]

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