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.