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: