Find rolling 12 month average trend

[Anonymous]

I have a custom measure named 'Measure_2' that evaluates and generates values to common unit. I have data ranging from 2018 to 2023. 

I would like to create a new DAX measure '12MonthRollingAvg' that will find the rolling average of past 12 months and another DAX measure 'Past12monthRollingAvg' that will find rolling average of 12 months a year before than current date.

I tried this DAX command but it fails.

12MonthRollingAvg =

VAR numOfMonths=12

VAR lastCurrentDate=MAX('Calendar_Month'[Calendar Month - Number])

VAR Period = DATESINPERIOD('Calendar_Month'[Calendar Month - Name], lastCurrentDate,-numOfMonths,MONTH)

VAR result = CALCULATE(

    AVERAGEX(

        VALUES('Calendar_Month'[Calendar Month - Number]), [Measure_2]),

        Period

)

Return

if ([Measure_2]>0 , result)



My desired visualization should look something like this: 

The blue line is for Measure '12MonthRollingAvg' and grey line is 'Past12MonthRollingAvg' 

My table for dates looks like this:

[ValtteriN]

Hi,

Try these kinds of structures:

12M AVG =

CALCULATE ( DIVIDE ( selectedmeasure(), 12 ), DATESBETWEEN ( 'Calendar'[Date], DATEADD ( LASTDATE ( DATEADD ( 'Calendar'[Date], -12, MONTH ) ), +1, DAY ), LASTDATE ( 'Calendar'[Date] ) ) )

PY 12M =

CALCULATE ( [12M AVG], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

 

[Anonymous]

Thanks for your comment. I tried out your DAX expression and tried to visualize it using spark lines. But the output isnt what I expected. I had to change your expression to accomodate my tables. This is how the DAX expression looks like now:

Rolling12Month = CALCULATE ( DIVIDE ( selectedmeasure(), 12 ), DATESBETWEEN ( 'Calendar_Month'[Calendar Month - Number], DATEADD ( LASTDATE ( DATEADD ( 'Calendar_Month'[Calendar Month - Number], -12, MONTH ) ), +1, DAY ), LASTDATE ( 'Calendar_Month'[Calendar Month - Number] ) ) )

However, the visualization with this still gives an error.