Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors