Skip to main content
cancel
Showing results for 
Search instead 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

Reply
NMehta2
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 RAQuick Measure
Client ABCABCB
Jan1471474
Feb4782.55.57.55.5
Mar5003.333.655.5 X
Apr00432.3347 X
May5573.331.663.65 X
Jun6783.6646.334 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 

Capture.JPG

3 REPLIES 3
v-diye-msft
Community Support
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.

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

NMehta2_1-1594663030371.png

 

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

ImkeF
Super User
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!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.