Reply
robertobrsp
Frequent Visitor

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:

PERSONCUSTOMER2024/012024/022024/032024/042024/052024/06
MARIAA101010101010
MARIAB  2020  
ALDOB    4545


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.



1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

Hi @robertobrsp, check this:

 

Output

dufoq3_0-1727460790511.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

3 REPLIES 3
dufoq3
Super User
Super User

Hi @robertobrsp, check this:

 

Output

dufoq3_0-1727460790511.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

lbendlin
Super User
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
avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)