Forum Discussion
Solved
How to Accumulate Values by Date and Customer, Resetting When the Assigned Person Changes?
Hi everyone, I have a table where one of the columns contains the date, and I need to accumulate values over time. However, if the customer switches to a different person on a given row, the accumulation needs to stop for the previous person and start accumulating for the new person.
Here’s a simplified example of the table:
Original table:
| DATE | |CUSTOMER | |PERSON | |VALUES |
| 2024/01 | |A | |MARIA | |10 |
| 2024/03 | |B | |MARIA | |20 |
| 2024/05 | |B | |ALDO | |25 |
And here’s the result I would like to achieve:
| PERSON | CUSTOMER | 2024/01 | 2024/02 | 2024/03 | 2024/04 | 2024/05 | 2024/06 |
| MARIA | A | 10 | 10 | 10 | 10 | 10 | 10 |
| MARIA | B | 20 | 20 | ||||
| ALDO | B | 45 | 45 |
Problem: The accumulation of values should continue month by month while the customer stays with the same person. When the customer changes to a new person, the accumulation for the previous person should stop, and a new line should start for the new person.
Hi robertobrsp, check this:
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE3MFTSUXIEYl/HIE8QbWigFKsDkzQGCjghSRohS5pCJR19XPxBcqZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, CUSTOMER = _t, PERSON = _t, VALUES = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"VALUES", type number}}), Helper = [ dates = List.Transform(ChangedType[DATE], (x)=> Date.FromText(x & "/01", [Format="yyyy/MM/dd"])), minDate = List.Min(dates), maxDate = List.Max(dates), headers = Table.FromList(List.Generate( ()=> minDate, each _ <= maxDate, each Date.AddMonths(_, 1), each Date.ToText(_, [Format="yyyy/MM", Culture="en-US"]))) ], StepBack = ChangedType, GroupedRows = Table.Group(StepBack, {"CUSTOMER"}, {{"All", each [ // _Detail = GroupedRows{0}[All], _Detail = _, _ShiftedValues = Table.FromColumns(Table.ToColumns(_Detail) & {{null} & List.RemoveLastN(_Detail[VALUES])}, Value.Type(Table.FirstN(_Detail, 0) & #table(type table[Prev VALUES = number], {}))), _Ad_NewValue = Table.AddColumn(_ShiftedValues, "NewValue", each [VALUES] + ([Prev VALUES] ?? 0), type number), _Ad_Index = Table.AddIndexColumn(_Ad_NewValue, "Index", 0, 1, Int64.Type), _MergedQueries = Table.NestedJoin(_Ad_Index, {"DATE"}, Helper[headers], {"Column1"}, "_Sorted", JoinKind.FullOuter), _Expanded = Table.ExpandTableColumn(_MergedQueries, "_Sorted", {"Column1"}, {"Column1"}), _Sorted = Table.Sort(_Expanded,{{"Column1", Order.Ascending}}), _FilledDown = Table.FillDown(_Sorted, Table.ColumnNames(_Sorted)), _RemovedColumns = Table.RemoveColumns(_FilledDown,{"DATE", "VALUES", "Prev VALUES"}), _Pivoted = Table.Pivot(_RemovedColumns, List.Distinct(_RemovedColumns[Column1]), "Column1", "NewValue"), _Filtered = Table.SelectRows(_Pivoted, each [CUSTOMER] <> null), _Sorted2 = Table.Sort(_Filtered,{{"Index", Order.Ascending}}), _RemovedColumns2 = Table.RemoveColumns(_Sorted2,{"Index"}) ][_RemovedColumns2], type table}}), Combined = Table.Combine(GroupedRows[All]) in Combined
3 Replies
- dufoq3
Community Champion
Hi robertobrsp, check this:
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE3MFTSUXIEYl/HIE8QbWigFKsDkzQGCjghSRohS5pCJR19XPxBcqZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, CUSTOMER = _t, PERSON = _t, VALUES = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"VALUES", type number}}), Helper = [ dates = List.Transform(ChangedType[DATE], (x)=> Date.FromText(x & "/01", [Format="yyyy/MM/dd"])), minDate = List.Min(dates), maxDate = List.Max(dates), headers = Table.FromList(List.Generate( ()=> minDate, each _ <= maxDate, each Date.AddMonths(_, 1), each Date.ToText(_, [Format="yyyy/MM", Culture="en-US"]))) ], StepBack = ChangedType, GroupedRows = Table.Group(StepBack, {"CUSTOMER"}, {{"All", each [ // _Detail = GroupedRows{0}[All], _Detail = _, _ShiftedValues = Table.FromColumns(Table.ToColumns(_Detail) & {{null} & List.RemoveLastN(_Detail[VALUES])}, Value.Type(Table.FirstN(_Detail, 0) & #table(type table[Prev VALUES = number], {}))), _Ad_NewValue = Table.AddColumn(_ShiftedValues, "NewValue", each [VALUES] + ([Prev VALUES] ?? 0), type number), _Ad_Index = Table.AddIndexColumn(_Ad_NewValue, "Index", 0, 1, Int64.Type), _MergedQueries = Table.NestedJoin(_Ad_Index, {"DATE"}, Helper[headers], {"Column1"}, "_Sorted", JoinKind.FullOuter), _Expanded = Table.ExpandTableColumn(_MergedQueries, "_Sorted", {"Column1"}, {"Column1"}), _Sorted = Table.Sort(_Expanded,{{"Column1", Order.Ascending}}), _FilledDown = Table.FillDown(_Sorted, Table.ColumnNames(_Sorted)), _RemovedColumns = Table.RemoveColumns(_FilledDown,{"DATE", "VALUES", "Prev VALUES"}), _Pivoted = Table.Pivot(_RemovedColumns, List.Distinct(_RemovedColumns[Column1]), "Column1", "NewValue"), _Filtered = Table.SelectRows(_Pivoted, each [CUSTOMER] <> null), _Sorted2 = Table.Sort(_Filtered,{{"Index", Order.Ascending}}), _RemovedColumns2 = Table.RemoveColumns(_Sorted2,{"Index"}) ][_RemovedColumns2], type table}}), Combined = Table.Combine(GroupedRows[All]) in Combined - lbendlin
Super User
why 45 and not 25?
The balance follows the customer, in this case, customer B
DATE | CUSTOMER | PERSON | VALUES 2024/03 | B | MARIA | 20 2024/05 | B | ALDO | 25 45
