Skip to main content
cancel
Showing results for 
Search instead 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

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

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? 

 

 Capture.PNG

 

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
amitchandak
Super User
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://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/

View solution in original post

6 REPLIES 6
amitchandak
Super User
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://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
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. 

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

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.

Top Solution Authors