Running Total start over
Date Expense Date Expense RTSO 01/01/2020 1 01/01/2020 1 1 02/01/2020 2 02/01/2020 2 3 03/01/2020 3 03/01/2020 3 6 04/01/2020 4 04/01/2020 4 10 05/01/2020 5 05/01/2020 5 15 06/01/2020 6 06/01/2020 6 21 31/01/2020 7 31/01/2020 7 28 01/02/2020 8 01/02/2020 8 8 02/02/2020 9 02/02/2020 9 17 05/02/2020 10 05/02/2020 10 27 02/03/2020 11 02/03/2020 11 11 03/03/2020 1 03/03/2020 1 12
Code:// Table1 let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Month = Table.Sort(Table.AddColumn(Source, "Month", each Date.Month([Date]), Int64.Type),{{"Month", Order.Ascending}}), TableType = Value.Type(Table.AddColumn(Source, "RTSO", each null, type number)), Group = Table.Group(Month, {"Month"}, {{"AllData", fnRTSO, TableType}}), Expand = Table.ExpandTableColumn(Group, "AllData", {"Date", "Expense", "RTSO"}, {"Date", "Expense", "RTSO"}), TypeDate = Table.TransformColumnTypes(Expand,{{"Date", type date}}), TSC = Table.SelectColumns(TypeDate,{"Date", "Expense", "RTSO"}) in TSC // fnRTSO (MyTable as table) as table => let Source = Table.Buffer(MyTable), TableType = Value.Type(Table.AddColumn(Source, "RTSO", each null, type number)), Cumulative = List.Skip(List.Accumulate(Source[Expense],{0},(Cumulative, Value) => Cumulative & {List.Last(Cumulative) + Value})), RTSO = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType) in RTSO




Bookmarks