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' t...
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.