Forum Discussion

TotalAnonymous's avatar
TotalAnonymous
Helper III
9 months ago
Solved

Moving Average 3 Months Value - Only Considering Not Blank

Dear Community, 

I hope you're doing well. I'm facing an issue with DAX calculation here. 

Currently, I'm trying to calculate Moving Average 3 Month value (Past 3 Months value) using this DAX:

However, It seems that the average result is not like Excel that only consider Non-Blank Value. 
In this case, Some SKUs have blank value in particular month. Hence, the average is a bit incorrect. 


For example:

For A-1 Product, the values are only in Aug and Oct 2025. 

 

Hence, the result is (-2846133 + (-1423067)) / 3 = -1423066.6667 (like the image below)

However, the expectation is only consider number of month that has sales value, which is 2 in this case. 


Do you have any suggestion how to adjust this formula? Thank you!

Please kindly find the PBIX here:
https://drive.google.com/file/d/12qSrwDDI3JJCudDA0qsmgjsEQS0jvzk2/view?usp=sharing

  • amitchandak's avatar
    amitchandak
    9 months ago

    TotalAnonymous , Try like 

    Rolling 3 = CALCULATE(Averagex(Values('Date'[Month Year]) ,[Latest Value]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-3,MONTH))

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    9 months ago

    TotalAnonymous , Try like 

    Rolling 3 = CALCULATE(Averagex(Values('Date'[Month Year]) ,[Latest Value]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-3,MONTH))

  • v-sshirivolu's avatar
    v-sshirivolu
    Community Support
    9 months ago

    Hi TotalAnonymous ,
    I would take a moment to thank amitchandak     , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
     

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions