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 4 weeks

.....

Could you please help me how to do that?

Thank you

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

8 Replies

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    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
      Frequent Visitor
      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
    Icon for Solution Supplier rankSolution 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
        Icon for Solution Supplier rankSolution 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.