Rolling 6 months with a variable number of months on the x-axis

[Jo_Swinnen]

Hi,

I've created several measures in my report to make visualisations with a variable number of months on the x-axis by using a numeric parameter 'number of months'.

For instance: 

Total= CALCULATE(sum([Revenue]),DATESINPERIOD('Dim Date'[Date],MAX('Dim Date'[Date]),-[Number of months],MONTH))

By placing a slicer on the month_year in Dim date and a slicer with the numeric parameter and on the x axis I placed a month_year column out of the fact table, the number of months are variable. 

But now I want the rolling 12 months also in this visualisation. 

But I get the same results as the total measure. 

I tried two measures:

Total 12 months = CALCULATE([Total], DATESINPERIOD('Dim Date'[Date],max('Dim Date'[Date]),-12,MONTH))

Total 12 months = CALCULATE(CALCULATE([Total],DATESINPERIOD('Dim Date'[Date],max('Dim Date'[Date]),-12,MONTH), DATESINPERIOD('Dim Date'[Date],MAX('Dim Date'[Date]),-[Number of months],MONTH))

Can anyone help?

Kind regards,

Jo 

[Anonymous]

Hi @Jo_Swinnen ,

You can use the SAMEPERIODLASTYEAR function to calculate the rolling 12 months based on the same period of the previous year. 

Total 12 months = CALCULATE([Total], SAMEPERIODLASTYEAR(‘Dim Date’[Date]))

You can use the PARALLELPERIOD function to calculate the rolling 12 months based on a specified number of intervals in the past or future.

Total 12 months = CALCULATE([Total], PARALLELPERIOD(‘Dim Date’[Date], -12, MONTH))

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.