cancel
Showing results for
Did you mean:  Helper II

## 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: 1 ACCEPTED SOLUTION  Community Support

Hi @sonya7 ,

``````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

3 REPLIES 3  Super User

@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  Helper II

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

Hi @sonya7 ,

``````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  