Adds a column with a rolling sum to a table.

Table.AddRollingSum

let func =   
 (Table as table, SortColumn as text, AmountColumn as text, NoOfMonthsToGoBack as number) =>
let

fnRunningTotal =  (Table as table, SortColumn as text, AmountColumn as text) =>

let
// Sort table and buffer it
    Sorted = Table.Buffer(Table.AddIndexColumn(Table.Sort(Table,{{SortColumn, Order.Ascending}}), "Index",1,1)),
// Select the Columns
    SelectColumns = Table.SelectColumns(Sorted, {SortColumn, AmountColumn, "Index"}),
// Extract Amount column and buffer it
    ExtractAmountColumn = List.Buffer(Table.Column(SelectColumns, AmountColumn)),
// Calculate a list with all running Totals
    RunningTotal = List.Skip(List.Generate( ()=> [ListItem=0, Counter=0]
                                            ,each [Counter] <= List.Count(ExtractAmountColumn)
                                            ,each [ ListItem = ExtractAmountColumn{[Counter]}+[ListItem],
                                                    Counter = [Counter]+1
                                                   ]
                                            ),1),
    ConvertedTable = Table.FromList(RunningTotal, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedColumn = Table.ExpandRecordColumn( ConvertedTable, "Column1", {"ListItem", "Counter"}, {"ListItem", "Counter"}),
    MergedQueries = Table.NestedJoin(Sorted,{"Index"}, ExpandedColumn,{"Counter"},"Expanded Column1",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn( MergedQueries, "Expanded Column1", {"ListItem"}, {"RunningTotal"}),
    #"Removed Columns" = Table.RemoveColumns(Expand,{"Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"RunningTotal", type number}})
in
    #"Changed Type"
,

    Source = Table,
    #"Inserted Month" = Table.AddColumn(Source, "StartDate", each Date.AddMonths(Record.Field(_, SortColumn), NoOfMonthsToGoBack*-1), type date),
    RT1 = fnRunningTotal(#"Inserted Month", SortColumn, AmountColumn),
    #"Merged Queries" = Table.NestedJoin(RT1,{"StartDate"},RT1,{SortColumn},"StartValue",JoinKind.LeftOuter),
    #"Expanded StartValue" = Table.ExpandTableColumn(#"Merged Queries", "StartValue", {"RunningTotal"}, {"StartValue"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Expanded StartValue", "RollingTotal", each [RunningTotal] - [StartValue], type number)
in
    #"Inserted Subtraction" ,
documentation = [
Documentation.Name =  " Table.AddRollingSum ",
Documentation.Description = " Adds a column with a rolling sum to a table.  ",
Documentation.LongDescription = " Adds a column with a rolling sum to a table. Table as first parameter, SortColumn as 2nd, AmountColumn as 3rd and Number of months to roll back as the 4th funciton parameter ",
Documentation.Category = " Table ",
Documentation.Source = " Imke Feldmann: www.TheBIccountant.com.  ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann: www.TheBIccountant.com.   ",
Documentation.Examples = {[Description =  "  ",
Code = "  ",
Result = "  "]}]
  
 in  
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))