Grouped Rolling Average

Hi pbix1 

This M code creates a rolling average based on the sample data you supplied.  It does not need an Index column to work.

It will work with any number of 'Keys'.

You can download a sample PBIX file here

let

GRAList = (values as list, grouping as list) as list =>

  let

    TheList = List.Generate
    ( 
        ()=> [ GRT = values{0}, GRA = values{0}, i = 0, j = 1 ],

        each [i] < List.Count(values),

        each try 
                 if grouping{[i]} = grouping{[i] + 1} then [GRT = [GRT] + values{[i]+1} , GRA = GRT/j, i = [i] + 1, j = [j] + 1]

                 else [GRT = values{[i]+1} , GRA = GRT/j, i = [i] + 1, j = 1]

             otherwise [i = [i] + 1],

        each [GRA]
    )
in
    TheList,


    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsovLUlVMDBU0lECYyMgYaAUq4MiAxI0A2JLdAljILYAYnNkCZBqEyg2QJcwBVkCMssQQwoqCiSMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Index = _t, Counter = _t, MAA = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Counter", Int64.Type}, {"MAA", Int64.Type}}),

    BufferedValues = List.Buffer(#"Changed Type"[Counter]),
    BufferedGrouping = List.Buffer(#"Changed Type"[Key]),

    GroupedRunningAverage = Table.FromList(GRAList(BufferedValues, BufferedGrouping), Splitter.SplitByNothing(), {"GRA"}, null, ExtraValues.Error),
    Columns = List.Combine({Table.ToColumns(#"Changed Type"),Table.ToColumns(GroupedRunningAverage)}),
    #"Converted to Table" = Table.FromColumns(Columns,List.Combine({Table.ColumnNames(#"Changed Type"),{"Rolling Avg"}})),
    #"Reordered Columns" = Table.ReorderColumns(#"Converted to Table",{"Key", "Index", "Counter", "Rolling Avg", "MAA"})
in
    #"Reordered Columns"

Regards

Phil

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

Hi. Thanks for all the responses.  Phil, I tried your method but I could only see how it worked for a rolling average which I had already had a method for. I was after a rolling average by group. I found this method, which after a bit of adapting, worked for what I needed. Link below in case it helps anyone else:

https://stackoverflow.com/questions/55337952/power-query-m-language-50-day-moving-average

Finally! found a solution. First, apply Index by product see this post for further details Then index again without criteria (index all rows) Then, apply below code
= Table.AddColumn(#"Previous Step", "Volume SMA(50)", each if [Index_byProduct] >= 50 then List.Average(List.Range(#"Previous Step"[Volume], ([Index_All]-50),50)) else 0),
For large dataset, Table.Buffer function is recommended after index-expand step to improve PQ calculation speed