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

    • 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.