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