cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 3 month Rolling Average

Hello,

I need help to create a Dynamic moving average of 3 months.

My data has events per Client and data is heirachical based on Event Date Column. below is an example table of what i want to create. the vaues are sum of events per month.

 3 Month RA Quick Measure Client A B C A B C B Jan 1 4 7 1 4 7 4 Feb 4 7 8 2.5 5.5 7.5 5.5 Mar 5 0 0 3.33 3.6 5 5.5 X Apr 0 0 4 3 2.33 4 7 X May 5 5 7 3.33 1.66 3.6 5 X Jun 6 7 8 3.66 4 6.33 4 X

i would like to create a 3 MRA in which the first month (Jan 2020) is basically the event count iteslf since there is no data for Dec 2019. Feb 3MRA is Avg of Jan+Feb while March onwards the 3MRA is average of Month, Month-1 and Month -2.

Currently if i create a Quick Measure (Rolling Average) - it does give me data - see example for Client B - BUT that is incorrect becuase its only dividing by month which has a Value and not a zero. So essentiall for client B, 3MRA for March should be 3.6 and not 5.5 which its showing.

how can i get this DAX running?

Note i already have a canlender table in my data set with a Hiearchy

3 REPLIES 3
Community Support

Hi @NMehta2

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Frequent Visitor

Hello @v-diye-msft and @ImkeF

i am still looking for a better alternative to what @ImkeF has suggested. the issue is that by dividing by 3, i immediately get the first month divided by 3 and that is not what i want. Quick measure gives me incorrect values (does not consider null values) and dividing by 3 gives me a wrong start of the data

Still looking for a DAX that will help me the correct values as indicated in the table above - COL B in BOLD

Super User

Hi @NMehta2 ,

replace the AVERAGEX in the formula by a SUMX and divide the result by 3 instead.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!