Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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!
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,
User | Count |
---|---|
123 | |
70 | |
67 | |
58 | |
53 |
User | Count |
---|---|
189 | |
94 | |
67 | |
63 | |
56 |