12 Month Rolling Annual Turnover

[Anonymous]

Hi All, 

I've been struggling with this one for a while. I thought i had it but now i'm realising my solution isn't at all dynamic as it doesn't work correctly when i select a slicer value. 

Basically i'm trying to calculate rolling annual turnover at each month. No matter what month i select, i need to show 12 months rolling annual turnover from that month. 

For example, if i select Jan 2018 on my slicer, i need to show rolling annual turnover from Feb 2017 to Jan 2018. So no matter what month i select on my slicer, it should roll back 12 months and it should be rolling. 

[Anonymous]

https://www.daxpatterns.com/cumulative-total/

[Stachu · Accepted solution]

try this, you need to use months from the calendar table in the visuals

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

[Anonymous]

Hi @Stachu 

Thank you so much! Is there a way to make it so that when i select a month on my report level slicer, it shows the rolling turnover 12 months prior. I'm getting the correct output, but when i slice, it just shows that one month that i select, but i want it to show the last 12 months from the selected date. 

[Stachu]

hmm, that's doable, but adds a lot of complexity. The solution I that comes to mind is having the slicer without active joins to data (basically a new table just for this use case)

other than additional table, the complexity is following - because there would be no active joins you would have to manually recreate the default join behaviour (i.e. having 201902 selected filters for that month). In practice you could use USERELATIONSHIP to activate the join (it has to be deactivated by default) for regular Turnover and all other measures that don't require the rolling logic as you define it