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

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.