- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
why 45 and not 25?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
07-02-2024 12:22 PM | |||
07-03-2024 01:19 AM | |||
06-11-2024 02:22 AM | |||
08-13-2024 09:19 AM | |||
Anonymous
| 06-21-2023 08:35 AM |