Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
doume06
Helper I
Helper I

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...

doume06_0-1707925786124.png

 

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:
doume06_1-1707926039237.png

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

If someone has an idea that would be great.

Thanks in advance.

Dominique

 

 
 
 
 

 

 

 

 

1 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.

 

View solution in original post

5 REPLIES 5
doume06
Helper I
Helper I

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

 

@doume06 

while still investigating the issue, 

 

you can use this measure : 

Daniel29195_1-1707933871329.png

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

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
Helper I
Helper I

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

Daniel29195
Super User
Super User

@doume06 

 

 

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

 

best regards

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.