Search code examples
indexingpowerqueryforecastingmoving-averagem

Moving Average Template in Power Query

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. strong text

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:

[![!['''][2]][2]

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

It's working but now I need to include the numbers in yellow for the next week.

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(yellow numbers)?

Output

enter image description here

So, what I'm trying to do is create the moving average for the next week the use this number as input to calculated the moving average for the 2nd week and do the same to calculate the 3rd week

Solution

  • Can you just add 3 three custom columns with

    = Date.AddDays([date],7)
    = Date.AddDays([date],14)
    = Date.AddDays([date],21)

    Then merge the data on top of itself three times using [product] and [date] matching against [product] and either [date1] or [date2] or [date3]. Expand the merged data to pull out the sales and volume from those 3 weeks. After the expands, each row will have that weeks own number, along with the numbers from the next 3 weeks. Average them

    Note this is not going to take the moving average from the first week and somehow use that in the following weeks calculations