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

Jo_Swinnen — Mon, 25 Sep 2023 19:31:15 GMT

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,

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

Anonymous — Wed, 27 Sep 2023 03:01:19 GMT

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.

topic Re: Rolling 6 months with a variable number of months on the x-axis in DAX Commands and Tips

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

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