Forum Discussion
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
- v-diye-msft
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!
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
