3 month Rolling Average

[NMehta2]

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 

 

[v-diye-msft]

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!

[NMehta2]

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 

[ImkeF]

Hi @NMehta2 ,

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