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 (
        KMR[MRDTE],
        MONTH(TODAY()) && YEAR(TODAY()),
        3,
        MONTH
    )
)
 
And the end result is not what I expect.
 

As I only get one value, which is wrong,  for April,  and nothing for the rest of the months. I would expect for April (480+384+576)/3 (April + May+ June) = 480, for May should be (384+576+576)/3 = 512 and so on. And according to my dax function,  I think it should start in May 

 

What am I doing wrong?

Thanks

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.

     

11 Replies

  • 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 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.  Create a relationship (Many to One and Single) from the MRdte column of the KMR table to the Date column of the Calendar Table.  To your visual, drag Year and Month name from the Calendar Table.  This DAX pattern should work

    Measure = calculate(average(kmr[mQty]),datesbetween(calendar[date],min(calendar[date]),edate(min(calendar[date]),3)))

    I have asumed that mqty is a column in the kmr table.

    • martipe1's avatar
      martipe1
      Helper II
      2 years ago

      Hi, Ashish.

      Thank you for your answer, I think I followed all the steps your recommended but no luck. Here are the details:

      This is calendar table:

      I created the relationship

      I previously had my matrix 

      I replaced the date column by the one in the calendar table

       

      I created the measure 

      Measure = calculate(average(kmr[mQty]),datesbetween(calendar[date],min(calendar[date]),edate(min(calendar[date]),3)))
      This is small example of my data:

      I would expect first row for January and February the measure to be 0, for April (0+40+206)/3=82 , May (40+206+58)/3= 101.3  , and June (206+58+4)/3 = 89.3
      But once I include the measure to my matrix I get some funny numbers

       

       I honestly don't know what I'm doing wrong., my best guess is that I would need to change last part of the measure, but if that's the case, it's beyond my Power Bi knowledge

      Thanks in advance for your comments.