Quick Measure Rolling 12 m Average

[sonya7]

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: 

[amitchandak]

@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]

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

[v-cgao-msft · Accepted solution]

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