Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Find rolling 12 month average trend

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:

 

2 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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.