cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors