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

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.