PQ - Running Total start over

[sandy666]

Running Total start over


DateExpenseDateExpenseRTSO


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



// 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