Need Help in Visualization for 13 week moving average

[YweiB]

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.

[Ashish_Mathur]

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

[YweiB]

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

DATE	Total Sales	Moving Avg
1/3/2022	1201
1/10/2022	2039
1/17/2022	2143
1/24/2022	2605
1/31/2022	2866
2/7/2022	2233
2/14/2022	2641
2/21/2022	2426
2/28/2022	2736
3/7/2022	3279
3/14/2022	3248
3/21/2022	4164
3/28/2022	4564
4/4/2022	2695
4/11/2022	2762
4/18/2022	2556
4/25/2022	2784
5/2/2022	2826
5/9/2022	3119
5/16/2022	3252
5/23/2022	3031
5/30/2022	3319
6/6/2022	2175
6/13/2022	3323
6/20/2022	3141
6/27/2022	4174
7/4/2022	2698
7/11/2022	2204
7/18/2022	2539
7/25/2022	3114
8/1/2022	2711
8/8/2022	2460
8/15/2022	2527
8/22/2022	2253
8/29/2022	2364
9/5/2022	2613
9/12/2022	2458
9/19/2022	3322
9/26/2022	3786
10/3/2022	2841
10/10/2022	2427
10/17/2022	2179
10/24/2022	2086
10/31/2022	2531
11/7/2022	2473
11/14/2022	2703
11/21/2022	2985
11/28/2022	2092
12/5/2022	2323
12/12/2022	3974
12/19/2022	4690
12/26/2022	2916
1/9/2023	1606
1/16/2023	2234
1/23/2023	2192
1/30/2023	2858
2/6/2023	2566
2/13/2023	2615
2/20/2023	2308
2/27/2023	3095
3/6/2023	3640
3/13/2023	3490
3/20/2023	4126
3/27/2023	4188
4/3/2023	2741
4/10/2023	3124
4/17/2023	2311
4/24/2023	2650
5/1/2023	2702
5/8/2023	3128
5/15/2023	3465
5/22/2023	2920
5/29/2023	2729
6/5/2023	3196
6/12/2023	4068
6/19/2023	3505

[Ashish_Mathur]

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. 

[YweiB]

Here is the sample data in pbi. 

Sample Data 
Could you help with me in this? Appreaciate your time and help in advance!

Best.

YB

[Ashish_Mathur]

That take me to a sign-in page.  Also, in the Excel file that you share, show the expected result.

[YweiB]

So sorry for the confusion.

Here is the google drive link, I uploaded the pbi file and excel data in here.

Link to sample data 

The expected chart is in pbi file.

Thank you so much! Appreciate for your time!

YB

[Ashish_Mathur]

Hi,

In your post dated June 23, you showed a Date column but in the Excel file that you have shared, there is no date column and therefore i am confused.  I will just share the DAX pattern with you.

1. Create a Calendar Table with
2. Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table
3. To your visual, drag Date to the X-axis
4. Write this measure

13 weeks moving average = averagex(calculatetable(values(calendar[date]),datesbetween(calendar[date],min(calendar[date])-84,max(calendar[date]))),[Total sales])

Hope this helps.

[YweiB]

Hi Ahish,

Could you give me some idea in how to visuliaze moving average in two conseuctive years like 2022-2023. and 2023 moving average is related to data in end of 2022. 

I already created the week rank for those data, I also have the week number for each data.

I want to put 2022 moving avergae line and 2023 moving average line to compare the performance of two years.  something like this chart. but the number can correctly reflect the moving average.

Thank you so much for your time to help me!

[amitchandak]

@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]

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,