Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Moving average in power query

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).

 

PeriodItemLocationForecastDesired Result: 3 Mo Moving Average
1/1/2018ACA1021.66667
1/1/2018BCA2022
1/1/2018ANY517.33333
1/1/2018BNY1523.33333
2/1/2018ACA2023.33333
2/1/2018BCA3033.66667
2/1/2018ANY4020.66667
2/1/2018BNY5017.33333
3/1/2018ACA525.66667
3/1/2018BCA139.33333
3/1/2018ANY232
3/1/2018BNY544.66667
4/1/2018ACA40 
4/1/2018BCA35 
4/1/2018ANY10 
4/1/2018BNY15 
5/1/2018ACA25 
5/1/2018BCA65 
5/1/2018ANY50 
5/1/2018BNY32 
6/1/2018ACA12 
6/1/2018BCA18 
6/1/2018ANY36 
6/1/2018BNY87 

 

Any help on this would be greatly appreciated!

Thanks!

Rick

Need Help

5 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

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

  • mfwatson's avatar
    mfwatson
    Frequent Visitor
    2 years ago

    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.