Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Beckster
Frequent Visitor

Rolling Monthly Average of Measure by Group

Hi guys

I need some help with calculating a moving average from a measure. Have spent all day on this and am stumped.

 

I have a calculated measure which measures frequency % uplift month-on-month per group (tag). What I am needing is a way to have a rolling average of the past 3 month uplifts. Eg May to June uplift was 10%, June to July uplift was 20% and July to August uplift was 10% so the 3 month rolling average would be 20%.

 

From here I was trying to create a measure which averages the MoM uplift, using summarize to create a monthly table but when I can get it to work (ie not error), the numbers are incorrect. I suspect it could be due to the fact my data is daily and I'm trying summarise/average as monthly.

 

I've tried creating the summarise as a calculated table, rather than having it all as one measure while I tried to work it out, but even this way I can't get it to work.

 

Any help would be appreciated. Below are screenshots of what I've done so far:

 

Below is the summarised table from where I need to summarise the %mth column for the past 3 months of each month.

 

Table summarising by group (tag) and monthTable summarising by group (tag) and month

This is a version of the formula where I was trying to do as a TOPN:

Top Tag Uplifts = TOPN(5,SUMMARIZE(vw_sf_cs_cases,vw_sf_cs_cases[tag__c],vw_dimdate[fmonth],"3mth % Avg",AVERAGEX(filter(all(vw_dimdate[date]),vw_dimdate[date]>DATEADD(vw_dimdate[date],-3,MONTH)&&vw_dimdate[date]<=max(vw_dimdate[date])),[% Tag MoM Uplift])),[% Tag MoM Uplift],ASC)

 

 

Thanks Guys!

 

1 REPLY 1
v-sihou-msft
Microsoft Employee
Microsoft Employee

@Beckster

 

According to your description, you want to calculate the move average of "%mth" for past three months. Right?

 

In this scenario, I think you can summarize a table to aggregate the "%mth" on month level. You should also have a full calendar table which is related to this summarized table. Then you can calculate the past three months "%mth" and divide by past three month.

 

[3 Month Moving Avg Corrected]=

CALCULATE(sum(Table[%mth]), 
          DATESINPERIOD(Calendar[Date], 
                        LASTDATE(Calendar[Date]),-3, Month
                       )
         )

 / 
CALCULATE(DISTINCTCOUNT(Calendar[Year Month]), 
          DATESINPERIOD(Calendar[Date],
                        LASTDATE(Calendar[Date]),-3,Month
                       )
         )

 

Reference:
Moving Averages, Sums, Etc.
Rolling 12 Months Average in DAX

 

Regards,

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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