How to Accumulate Values by Date and Customer, Resetting When the Assigned Person Changes?

[robertobrsp]

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.

[dufoq3 · Accepted solution]

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]

why 45 and not 25?

[robertobrsp]

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