Forum Discussion

YweiB's avatar
YweiB
Frequent Visitor
3 years ago

Need Help in Visualization for 13 week moving average

Hi all,

 

Currently I have a list of sales data from 1/7/2019 to 6/19/2023. I used window function to calculate the rolling 13 week average for this sales data.

Here is the function of Rolling average: 

 

 

 

13 WK MA V2 = 
AVERAGEX(
    WINDOW(
        -12,REL,0,REL,
        SUMMARIZE(ALLSELECTED(Salesdata),'Date'[DATE]),
        ORDERBY('Date'[DATE],asc)
        
    ), 
    'KPI List'[Total Sales]
)

 

 

 

I want to create the line chart to compare the 2022 rolling 13 wk sales data vs 2023 rolling 13 wk sales data. But I have some problem in display the data. When I use the filter, the rolling avergae calculation will recalculate, based on filtered data. But What I want is used the data that after calculating to create line chart. Does anyone can help me with this question?

 

Thanks a lot.

Need Help
Tips and Tricks

10 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    YweiB , Create a date table join with date and use week rank column

     

    13 WK MA V2 =
    calculate(
    AVERAGEX(values('Date'[Week Rank])
    ,
    'KPI List'[Total Sales]
    ) ,WINDOW(
    -12,REL,0,REL,
    ALLSELECTED('Date'[Week Rank]),
    ORDERBY('Date'[Week Rank],asc)

    ))

     

     

    Week Rank

     

    Have these new columns in Date Table, Week Rank is Important in Date/Week Table

    Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

     

     

     

    • YweiB's avatar
      YweiB
      Frequent Visitor
      3 years ago

      Thank you Amitchandak.

       

      I just created the Week Rank and use the window function to calculate the 13 weeks moving average.

       

      Could you tell me how to viualize this moving average in line chart to compare the 2022 and 2023 with week rank? adding the filter 2023 will make the number of first 12 week off. how to make the number is cut by filters. I just want to display the number of moving average

       

      Thanks,

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Your question is not clear.  Share some data to work with, explain the question and show the expected result.

    • YweiB's avatar
      YweiB
      Frequent Visitor
      3 years ago

      Here is sample data from 2022-2023(number was randomly created).

      Now I want to use my moving average function to calculate 13 week moving average.

      And then create a line chart to compare the 2022 13 wk moving average trend with 2023 moving average trend, based on the calculation.

       

      But when I filtered the data for 2023, moving average will recalculate with fitered data. it means the number of first 12 week is not accurate, becasue we miss the data from 2022 to calculate the first 12 week moving average in 2023.

      Expected result is two lines in one line chart. one is 2022 moving average trend and 2023 moving average trend that first 12 week MA calculation use the data from 2022

      DATETotal SalesMoving Avg
      1/3/20221201 
      1/10/20222039 
      1/17/20222143 
      1/24/20222605 
      1/31/20222866 
      2/7/20222233 
      2/14/20222641 
      2/21/20222426 
      2/28/20222736 
      3/7/20223279 
      3/14/20223248 
      3/21/20224164 
      3/28/20224564 
      4/4/20222695 
      4/11/20222762 
      4/18/20222556 
      4/25/20222784 
      5/2/20222826 
      5/9/20223119 
      5/16/20223252 
      5/23/20223031 
      5/30/20223319 
      6/6/20222175 
      6/13/20223323 
      6/20/20223141 
      6/27/20224174 
      7/4/20222698 
      7/11/20222204 
      7/18/20222539 
      7/25/20223114 
      8/1/20222711 
      8/8/20222460 
      8/15/20222527 
      8/22/20222253 
      8/29/20222364 
      9/5/20222613 
      9/12/20222458 
      9/19/20223322 
      9/26/20223786 
      10/3/20222841 
      10/10/20222427 
      10/17/20222179 
      10/24/20222086 
      10/31/20222531 
      11/7/20222473 
      11/14/20222703 
      11/21/20222985 
      11/28/20222092 
      12/5/20222323 
      12/12/20223974 
      12/19/20224690 
      12/26/20222916 
      1/9/20231606 
      1/16/20232234 
      1/23/20232192 
      1/30/20232858 
      2/6/20232566 
      2/13/20232615 
      2/20/20232308 
      2/27/20233095 
      3/6/20233640 
      3/13/20233490 
      3/20/20234126 
      3/27/20234188 
      4/3/20232741 
      4/10/20233124 
      4/17/20232311 
      4/24/20232650 
      5/1/20232702 
      5/8/20233128 
      5/15/20233465 
      5/22/20232920 
      5/29/20232729 
      6/5/20233196 
      6/12/20234068 
      6/19/20233505 
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        3 years ago

        Hi,

        Based on that data that you have shared, show the expected result in Table format.  Once we get the correct numbers in the Table, we can create a line chart.  Also, in the PBI file that you share, ensure that you have a Calendar table with a week number column.