Forum Discussion

martipe1's avatar
martipe1
Helper II
2 years ago
Solved

Moving Average

I need a moving average in the future and I expect to have a different value every month, the dax function I use is:   AVRG = CALCULATE (     AVERAGEX ( KMR, KMR[MQTY] ),     DATESINPERIOD ( ...
Need Help
Show and Tell
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    You are welcome.  This measure works

    Measure = divide(calculate(sum(kmr[mQty]),datesbetween(calendar[date],min(calendar[date]),eomonth(min(calendar[date]),2))),3)

    Hope this helps.

     

sergej_og's avatar
sergej_og
Super User
2 years ago

Hey martipe1 ,
you would like to have smtg like this a result, right?

You have two options. Depends on your requirements.
 - write DAX (which will consider year change)
 - write a visual calculation (considering only data visible in visual)


DAX (no visual calc):

Rolling Avg = 
VAR Period = 
    DATESINPERIOD(
        'Date'[Date],
        MAX('Date'[Date]),
        -3,
        MONTH)

RETURN
CALCULATE(
    AVERAGEX(
        VALUES('Date'[Year Month Number]),
        [Qty]),
    Period)


 Visual Calc:

Rolling Avg (VC) = MOVINGAVERAGE([Qty], 3)


Hope you got the idea.


Regards.

martipe1's avatar
martipe1
Helper II
2 years ago

Thank you very much for your answer.

 

It's almost what I'm looking for, let me elaborate on what I want to achieve.

 

The data I posted above is the forecast, I want to calculate the inventory turnover for the current month based on the average of the forecast for next three months, I need to see to the future. E.g. I have a forecast for May, June, and July of 10, 20, & 30 respectively, the average is 20 if I have an inventory of 45 my inventory turnover is 2.25 month.

 

I think the DAX you were so kind to share with me is almost the same as the one I created, because instead of -3 I consider +3 (looking into the future), still not getting the expected result.

 

Thanks in advance for your comments