Forum Discussion

sonya7's avatar
sonya7
Helper III
3 years ago
Solved

Quick Measure Rolling 12 m Average

Hello everyone, I'm trying to write the results I provided with quick measure without using a date hierarchy in measure. can you help me?   measure:   
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi sonya7 ,

    Please try:

    12 month avg expense = 
VAR _max_date = MAX('Calendar'[Date])
VAR _min_date = EDATE(_max_date,-12)
VAR _amount = CALCULATE(SUM('Table'[Sales]),'Calendar'[Date]>_min_date&&'Calendar'[Date]<=_max_date)
VAR _avg = DIVIDE(_amount,12)
RETURN
_avg

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

amitchandak's avatar
amitchandak
Super User
3 years ago

sonya7 , Can you try a measure like this with help from date table

 

12 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value)))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

 

 

Rolling Months Formula: https://youtu.be/GS5O4G81fww

 

Average of Rolling, Average of Snapshots: https://youtu.be/_pZRdLAJxxA

  • sonya7's avatar
    sonya7
    Helper III
    3 years ago

    it didn't work for me. What is the month year field?

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi sonya7 ,

      Please try:

      12 month avg expense = 
VAR _max_date = MAX('Calendar'[Date])
VAR _min_date = EDATE(_max_date,-12)
VAR _amount = CALCULATE(SUM('Table'[Sales]),'Calendar'[Date]>_min_date&&'Calendar'[Date]<=_max_date)
VAR _avg = DIVIDE(_amount,12)
RETURN
_avg

      Best Regards,
      Gao
      Community Support Team

       

      If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

      How to get your questions answered quickly -- How to provide sample data