Moving Average

[martipe1]

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

[Ashish_Mathur]

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]

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.

[Ashish_Mathur]

Hi,

Share the download link of the PowerBI file.

[martipe1]

Thank you again for your answer.

Here it is an extract of my file Moving Average 

[Ashish_Mathur · Accepted solution]

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]

Thank you very much @Ashish_Mathur !!!

That did work perfectly.

I have another question regarding this measure, but will post it as another question and will tag you and mark this answer as accepted solution.

[Ashish_Mathur]

You are welcome.

[sergej_og]

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]

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]

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]

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.