topic Re: 3 Month Rolling Average Measure in DAX Commands and Tips

3 Month Rolling Average Measure

Anonymous — Thu, 09 Feb 2023 05:18:21 GMT

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

Re: 3 Month Rolling Average Measure

v-zhangti — Mon, 13 Feb 2023 05:56:35 GMT

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.

Re: 3 Month Rolling Average Measure

Anonymous — Tue, 14 Feb 2023 02:45:57 GMT

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