Rolling Monthly Average of Measure by Group

[Beckster]

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

[v-sihou-msft]

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