Moving average in power query

[Anonymous]

Hi all,

I am fairly new to Power Query Editor and I am having trouble calculating a moving average for use a a reference table for Power Bi report.  I need some help to calculate a moving average of forecasted unit sales over future monthly periods where the start moth of the list of forecasted units start with the next month (ie  offset to future by 1 month) and the next N nubmer of months are used in the moving average calculation.  This average needs to be calculated for each unique instance of month (in date format), item, and location.

My attempt at this is shown below, but this does not work and only generates an average for the firast instance of the item over the next 7 periods.

let
    Source = ""This is from CSV table locally sourced, see table included below for source"
    #"Kept First Rows" = Table.FirstN(Source,500),
    MyData = Table.Sort(#"Kept First Rows",{{"Item", Order.Ascending},{"Location", 
Order.Ascending},{"period", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(ExtractMyData, "Table Create", each MyData),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "List of Forecast/Act", each [Table Create][#"Forecast/Act Units"]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "List Range", each List.Range([#"List of Forecast/Act"],1,7)),
    #"Added Custom4" = Table.AddColumn(#"Added Custom2", "average", each List.Average([List Range]))
in
    #"Added Custom4"

Below is a sample of data that I am working with and the desired result.  In this case 6 months of data are provided for two items at two locations, and a 3 month moving average is calcuated for each item location for each period (N = 3).

Period	Item	Location	Forecast	Desired Result: 3 Mo Moving Average
1/1/2018	A	CA	10	21.66667
1/1/2018	B	CA	20	22
1/1/2018	A	NY	5	17.33333
1/1/2018	B	NY	15	23.33333
2/1/2018	A	CA	20	23.33333
2/1/2018	B	CA	30	33.66667
2/1/2018	A	NY	40	20.66667
2/1/2018	B	NY	50	17.33333
3/1/2018	A	CA	5	25.66667
3/1/2018	B	CA	1	39.33333
3/1/2018	A	NY	2	32
3/1/2018	B	NY	5	44.66667
4/1/2018	A	CA	40
4/1/2018	B	CA	35
4/1/2018	A	NY	10
4/1/2018	B	NY	15
5/1/2018	A	CA	25
5/1/2018	B	CA	65
5/1/2018	A	NY	50
5/1/2018	B	NY	32
6/1/2018	A	CA	12
6/1/2018	B	CA	18
6/1/2018	A	NY	36
6/1/2018	B	NY	87

Any help on this would be greatly appreciated!

Thanks!

Rick

[mfwatson]

In general I agree that DAX may be appropriate. However, my experience is that moving averages are compute intensive and performance can be dramatically improved by pre-calculating them.

[v-easonf-msft]

Hi , @Anonymous 

Create a custom column as below in powerquery:

= Table.AddColumn(#"Changed Type", "Custom", each let i = [Item],
L = [Location],
p =[Period],
t = Table.SelectRows(#"Changed Type",each [Item]=i and [Location] = L and [Period]>p and [Period]<=p+#duration(92,0,0,0)),
maxP = List.Max(Table.ToColumns(Table.SelectColumns(
t,{"Period"})){0}),
monthN = (Date.Year(maxP)*100+Date.Month(maxP) - Date.Year(p)*100-Date.Month(p) )
in
if monthN<3 or monthN=null then null else
List.Sum(Table.ToColumns(Table.SelectColumns(
t,{"Forecast"})){0})/monthN)

Here is a demo

pbix attached

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Eason,

Thansk for the info and reply.  I tried it out and it fundamentally works.

My issue now is that I am applying this to an appended table of about 1,000,000 rows of data, and it bombs out before completion (memory issues Im fairly certain).

Any work arounds to make this more efficient?

thanks again for the help,

Rick

[edhans]

You might want to check out this article by @ImkeF 

Article Link

[Ashish_Mathur]

Hi,

Such a calculation should be accomplished via DAX measures (not via the Mashup language of the Query Editor).