Forum Discussion

doume06's avatar
doume06
Helper I
2 years ago
Solved

Help on rolling average

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

 

 
 
 
 

 

 

 

 

General Comment
  • doume06's avatar
    doume06
    2 years ago

    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.

     

5 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    doume06 

     

     

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

     

    best regards

    • Daniel29195's avatar
      Daniel29195
      Community Champion
      2 years ago

      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's avatar
        doume06
        Helper I
        2 years ago

        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.