Forum Discussion

marenecaCZ's avatar
marenecaCZ
Frequent Visitor
3 years ago
Solved

Rolling average for weekly values

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...
Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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's avatar
    marenecaCZ
    3 years ago

    Hello all,

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

    Thank you all for your help.

    Marek

Alf94's avatar
Alf94
Solution Supplier
3 years ago

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's avatar
marenecaCZ
Frequent Visitor
3 years ago

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

Marek

  • Alf94's avatar
    Alf94
    Solution Supplier
    3 years ago

    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's avatar
      marenecaCZ
      Frequent Visitor
      3 years ago

      Alf94 , something is wrong in my measure:

       

       

      • Alf94's avatar
        Alf94
        Solution Supplier
        3 years ago

        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.