Get rolling average of a Measure

[swisdom]

I have two datasets, a series of Returns and a series of Shipments. I have a measure calculating the monthly Return Rate over the running total of shipments. I would like to create another measure which is a rolling 6 month average of that Return Rate, but the DAX code I have written is not doing what I want. Its simple enough in Excel but I'm having some trouble with the DAX.

Link to sample .pbix file 

The code I have used:

6 Mo Avg Rate = 
VAR NumOfMonths = 6
VAR LastCurrentDate =
    MAX ( 'Date'[Date] )
VAR Period =
    DATESINPERIOD ( 'Date'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
    CALCULATE (
        AVERAGEX (
            VALUES ( 'Date'[Date] ),
            [Return Rate]
        ),
        Period
    )
VAR FirstDateInPeriod = MINX ( Period, 'Date'[Date] )
VAR LastDateWithRMA = MAX ( 'RMA'[Date Submitted] )
RETURN
    IF ( FirstDateInPeriod <= LastDateWithRMA, Result )

Results I get from that code:

Results I would *expect* to get:

Any tips?

[aj1973 · Accepted solution]

Hi @swisdom 

In your date table add a column for Year Month

in your measure replace Date by Year Month

Replace the fields Calendar Year and Month by Year Month

Result

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak]

@swisdom , Try with Month Year

change this

VAR Result =
    CALCULATE (
        AVERAGEX (
            VALUES ( 'Date'[Year Month] ),
            [Return Rate]
        ),
        Period
    )

[swisdom]

That didn't seem to quite do the trick but its a little more normal?

[aj1973 · Accepted solution]

Hi @swisdom 

In your date table add a column for Year Month

in your measure replace Date by Year Month

Replace the fields Calendar Year and Month by Year Month

Result

[swisdom]

So weird I did exactly that and I'm still getting the same results? Can you take a look at my file below and double check? I see that you got the correct results in your version so I'm not sure what I did wrong.

Link to file 

[parry2k]

Well done @Ashish_Mathur 👍

[Ashish_Mathur]

Thank you @parry2k 

[aj1973 · Accepted solution]

@swisdom 

In your measure change these 2

Also don't forget to render the column Year Month to Date format

Let me know otherwise I will send you the file.

[swisdom]

That did it, thank you so much!

[aj1973]

@swisdom Welcome, eventhough I thought @Ashish_Mathur solution was more close to what you were looking for.