3 Month Rolling Average Measure

[Anonymous]

Hello looking for help adding a rolling 3-month average to the below table in power bi. 

Dummy values in the Rolling Avg 3M column im attempting to populate via a measure. 

For the retention rate (rr) is 50 for feb as seen below, so the Rolling Average should show 50. 

The next month march is 60, so the Rolling Average should show 50+60/2 = 55

Then apr which is 70,  so the Rolling Average should show 50+60+70/2 = 60

Once we move into May, then the rolling average should do March/Apr/May, and so on. 

table name rrate

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.
Measure:

Rolling Avg 3M = 
Var _N1=SUMMARIZE(FILTER(ALL('Table'),[Date]<=SELECTEDVALUE('Table'[Date])),[Date],"Sum",SUM('Table'[Value]))
Var _N2=TOPN(3,_N1,[Date],DESC)
Return
DIVIDE(SUMX(_N2,[Sum]),COUNTROWS(_N2))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Looks great @v-zhangti Thank you for taking the time to help, its much appreciated.