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.

     

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

sergej_og's avatar
sergej_og
Super User
2 years ago

ah, ok.
Let`s try to tweek the fomula.
I hope I get the idea right.


Formula used:

Rolling Avg (FC) = 
VAR Period = 
    DATESINPERIOD(
        'Date'[Date],
        MIN('Date'[Date]),
        3,
        MONTH)

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

 
Try out.

Regards 

  • martipe1's avatar
    martipe1
    Helper II
    2 years ago

    Once again, thank you for your input
    I tried to copy your DAX
    and replace your columns by the columns I use, all are in the same table.
    First part I replace your Date column by mine MRDTE which is a Date/Time type (Hierarchy), because I want to use it for incremental refresh. Any comment?
    I have no problem replacing columns with the first part (VAR).
    My problem starts with the IF functions as when I try to replace you Qty by mine MQTY, it doesn't show the column when I start typing.
    What am I doing wrong?
    Thanks in advance for your help.