Rolling average for weekly values

[marenecaCZ]

Hello, 

my table with data has only two columns:

I would like to create a new table (visual) with rolling average measure:

week 9 = average for last 4 weeks

week 10 = average for last 4 weeks

.....

Could you please help me how to do that?

Thank you

[v-zhangti · Accepted solution]

Hi, @marenecaCZ 

You can try the following methods.
Measure:

4 week moving average = 
Var _N1=SUMMARIZE(FILTER(ALL('Table'),[Week]<=MAX('Table'[Week])),[Week],"Sum",SUM('Table'[Count]))
Var _N2=TOPN(4,_N1,[Week],DESC)
Var _Average=DIVIDE(SUMX(_N2,[Sum]),4)
return
IF(COUNTX(_N2,[Sum])<4,BLANK(),_Average)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-zhangti · Accepted solution]

Hi, @marenecaCZ 

You can try the following methods.
Measure:

4 week moving average = 
Var _N1=SUMMARIZE(FILTER(ALL('Table'),[Week]<=MAX('Table'[Week])),[Week],"Sum",SUM('Table'[Count]))
Var _N2=TOPN(4,_N1,[Week],DESC)
Var _Average=DIVIDE(SUMX(_N2,[Sum]),4)
return
IF(COUNTX(_N2,[Sum])<4,BLANK(),_Average)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[marenecaCZ · Accepted solution]

Hello all,

I found the solution. In my data model, it looks like this:

Thank you all for your help.

Marek

[Alf94]

Hello @marenecaCZ,

I think the following may do the trick:

Rolling Average = 

    VAR rollingValue = -3
    
    RETURN
        IF(
            RANKX(
                ALLSELECTED( 'Table'[Week] ),
                CALCULATE( MAX( 'Table'[Week] ) ),
                ,
                ASC
            )
            > ABS( rollingValue ),
            AVERAGEX(
                WINDOW(
                    rollingValue, REL,
                    0, REL,
                    ORDERBY( 'Table'[Week], ASC )
                ),
                CALCULATE( SUM( 'Table'[Count] ) )
            ),
            BLANK()
        )

Let me know if this is ok.

[marenecaCZ]

Hello @Alf94 , thank you, but it doesn't work. Something is wrong maybe in the ORDERBY function 😞

Marek

[Alf94]

@marenecaCZ, can you share more details about what is not working please? It is working on my side, as you can see on this screenshot:

The ORDERBY function is underlined in red but works, this is a known bug.

[marenecaCZ]

@Alf94 , something is wrong in my measure:

[Alf94]

@marenecaCZ, the problem comes from the WINDOW() function. Your version of Power BI Desktop must be at least December 2022 to use this function, which doesn't seem to be the case now. Try upgrading your version and everything should be fine afterwards.

[marenecaCZ]

@Alf94 thanks a lot. Unfortuntely I can't upgrade to Dec 2022 version due to some IT restrictions in the company where I work 😞 So I will have to look for another solution. But thanks a lot.