cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## 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 III

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors