Help on rolling average

[doume06]

Dear community,

I reproduced an issue I face on rolling average calculation:

I want to calculate a rolling average on 3 months in a table of 12 months and twelve values:

I have a calendar table.

Here is my basic table and the expected average calculated in Excel: so the first 20 is the average of 10,20,30 and etc...

Here is my formula of the rolling average:

average 3 month =

VAR lastmonth = MAX('Calendar'[Date])

VAR Last3MonthsAvgValue =

    CALCULATE(

        AVERAGEX(

            DATESINPERIOD(

                'Calendar'[Date],

                lastmonth,

                -3,

                MONTH

            ),

            [value test]

        )

    )

RETURN Last3MonthsAvgValue

Measure value test:

value test =

    LOOKUPVALUE(

        RAW[value],

        RAW[month],

        MAX('Calendar'[YearMonth])

    )

RAW is my RAW table displayed above

Here is the result:

I don't understand why there are these tiny differences.

If someone has an idea that would be great.

Thanks in advance.

Dominique

[doume06]

Hi Daniel,

Thanks for your answer.

Here we go: .pbix and RAW file

https://we.tl/t-qzpgDgCZ6E

Tell me if you see something wrong.

Thanks a lot for your help.

Dominique

[Daniel29195]

@doume06 

while still investigating the issue, 

you can use this measure : 

new measure = 
var s = 
SUMX(
    WINDOW(
        -2,REL,
        0,REL,
        SUMMARIZE(allselected(RAW),RAW[month],RAW[value]),
        ORDERBY(RAW[month], ASC)
    ),
    RAW[value]
)

return s / 3

[doume06 · Accepted solution]

Thanks Daniel, WINDOW function works fine in the table. Now my real dataset is not a table but a measure so it's a different approach. I will try to find the solution with what you gave me.

Thanks again for your help.

[doume06]

Sure but I can't see how to load the file

[Daniel29195]

@doume06 

if possible, could you please the pbix file so i can take a closer look ? 

best regards