Moving average 3 weeks

donpep0 · ‎08-29-2022

I am new to Power Query and I'm trying to create a query template that applies a 3 moving average to the volume and sales for the next 3 weeks grouped by weekday for each product.

My input table looks like this.

INPUT
SPAN	07/01/2022	Fri	345	589
SPAN	07/08/2022	Fri	453	124
SPAN	07/08/2022	Fri	453	124
SPAN	07/15/2022	Fri	789	793
SPAN	07/22/2022	Fri	564	464
SPAN	07/29/2022	Fri	645	548
PC	07/01/2022	Fri	689	645
PC	07/08/2022	Fri	564	646
PC	07/08/2022	Fri	646	754
PC	07/15/2022	Fri	564	744
PC	07/22/2022	Fri	549	646
PC	07/29/2022	Fri	979	319
SPAN	07/06/2022	Wed	235	542
SPAN	07/13/2022	Wed	234	589
SPAN	07/20/2022	Wed	354	545
SPAN	07/27/2022	Wed	545	435
PC	07/06/2022	Wed	656	646
PC	07/13/2022	Wed	968	346
PC	07/13/2022	Wed	574	731
PC	07/20/2022	Wed	878	464
PC	07/27/2022	Wed	579	643

Note: I didn't include all the data but there is data for each weekday (Mon, Tue, Wed, Thu, Fri, Sat, Sun) and there are more products.

Below are the steps that I've been doing:
Sort the data first by call type and then by weekday.
Then grouped the rows and create an index for each product-weekday with the next code:

#"Grouped Rows" = Table.Group(#"Sorted Rows", {"product", "weekday"}, {{"Index", each
Table.AddIndexColumn(_,"Index", 1,1), type table}}),

#"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"date", "volume", "sales",
"Index",} , {"Index.date", "Index.volume", "Index.sales", "Index.Index",}

Then I apply the 3 moving Average to the volume:
step1 = Table.AddColumn(#"Expanded Index", "Average_vol", each if [Index.Index] > 2 then List.Average(List.Range(#"Expanded Index"[Index.volume], _[Index.Index] -3, 3)) else null, type number)

So now I have this:

SPAN	07/06/2022	Wed	235	542	1	Null
SPAN	07/13/2022	Wed	234	589	2	Null
SPAN	07/20/2022	Wed	354	545	3	558.666667
SPAN	07/27/2022	Wed	545	435	4	523
SPAN	07/01/2022	Fri	345	589	1	Null
SPAN	07/08/2022	Fri	453	124	2	Null
SPAN	07/08/2022	Fri	453	124	3	279
SPAN	07/15/2022	Fri	789	793	4	347
SPAN	07/22/2022	Fri	564	464	5	460.333333
SPAN	07/29/2022	Fri	645	548	6	601.666667
PC	07/06/2022	Wed	656	646	1	Null
PC	07/13/2022	Wed	968	346	2	Null
PC	07/13/2022	Wed	574	731	3	574.333333
PC	07/20/2022	Wed	878	464	4	513.666667
PC	07/27/2022	Wed	579	643	5	612.666667
PC	07/01/2022	Fri	689	645	1	Null
PC	07/08/2022	Fri	564	646	2	Null
PC	07/08/2022	Fri	646	754	3	681.666667
PC	07/15/2022	Fri	564	744	4	714.666667
PC	07/22/2022	Fri	549	646	5	714.666667
PC	07/29/2022	Fri	979	319	6	569.666667

the highlighted cells are the moving average expected for the next week.

It's working but now I need to include the numbers in red for the "next 2-week average" and the 3rd

So now I'm stuck on that step and I need to create the template.

Do you know how can I apply the moving average for the next 2 weeks including the calculated numbers for the next week(red numbers)?

v-jingzhang · ‎09-01-2022

Hi @donpep0

What do you currently have for the next 2nd and 3rd week? If you have null values in them, you can possibly use Fill Down feature. After that, transform the column to show null for index 1 and 2.

I'm not sure how to tell which week is expected to be the current week thus which values should be expected for next weeks. Can you explain the logic why you highlight those values? Is it because the latest week in the table is the week that 07/29/2022 lies in? If so, do we need to add new rows for those "next dates"?

Best Regards,
Community Support Team _ Jing