Quarterly sum of 3 month rolling average

[tinkertrust]

Hi all, 

I have monthly results that I need to present as a 3 month rolling average, sometimes at a monthly level and sometimes as a sum of the 3 month rollling average for the quarter/year. 

At the monthly level I've found the below formula to work well:  

3-Mo Rolling Avg = CALCULATE([Market Performance], DATESINPERIOD(Calendar_Lookup[date], MAX(Calendar_Lookup[date]), -3, MONTH))

/ CALCULATE(DISTINCTCOUNT(Calendar_Lookup[Year_Month]), DATESINPERIOD(Calendar_Lookup[date], LASTDATE(Calendar_Lookup[date]), -3, MONTH))

But, when I show quarterly or annual results it shows one 3 month average instead of a sum of the 3 month averages for the period. How would you solve this?

The options I can see are:

1. Create a column in either Power Query or DAX that holds the 3 month averages so then I can SUM them as needed? Any advice on how to do this?
2. Figure out how to do a SUM of a measure. Any advice on how to do this?
3. Build a series of measures at the monthly, quarterly and annual level. Not a great solution as it doesn’t allow me to work fast when performing analysis because I have to be careful of pulling the right measure.

Any advice would be appreciated! Thanks!

[v-piga-msft]

Hi @tinkertrust,

Have you solved your problem?

If you have solved, could you please share the solution or accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, could you share some data sample and your desired output?

Best Regards,

Cherry

[Seward12533]

Try a measure that sums the 3 month rollling average for the last month in the quarter displayed then add the 2month rilling avg of the prior month the. Add the second prior long. This is accomplished by using a calculate and filtering your date table.

SumOfRollingAvgsLat3Months =
VAR maxdate = MAX(date[date])
VAR maxdate-1 = DATE(YEAR(maxdate),MONTH(maxdate)-1,1)
VAR maxdate-2 = DATE(YEAR(maxdate),MONTH(maxdate)-2,1)
RETURN Calculate([3-Mo Rolling Avg],FILTER(date,date[Year Month]=YEAR(maxdate)&MONTH(maxdate)) + Calculate([3-Mo Rolling Avg],FILTER(date,date[Year Month]=YEAR(maxdate-1)&MONTH(maxdate-1)) + Calculate([3-Mo Rolling Avg],FILTER(date,date[Year Month]=YEAR(maxdate-2)&MONTH(maxdate-2))

Will depend on your date table etc. but this approach should work.