Forum Discussion
Convert transactions to balance
Yes, I'd say the measure is the way to go here. It should work as desired in the chart if you take the date-field from your date-table and not from the transactions-table.
Link to file: https://www.dropbox.com/s/dc8budcqd68ogz4/PBI_CumTotalAllDates.pbix?dl=0
Community ChampionHi MarcelBeug,
that's an interesting approach. If you still have the test-environment with the large datasets, I'd be interested how the performance compares to this function:
(SourceTable as table, GroupColumns as list) =>
let
Source = SourceTable,
ChgType = Table.TransformColumnTypes(Source,{{"Date", type date}}),
Partitions = Table.Buffer(Table.Group(ChgType, GroupColumns , {{"Partition", each _}})),
AddAllDates = Table.AddColumn(Partitions, "Dates", each List.Transform({Number.From(List.Min(ChgType[Date]))..Number.From(List.Max(ChgType[Date]))}, each Date.From(_))),
ExpandDates = Table.ExpandListColumn(AddAllDates, "Dates"),
AddRT = Table.AddColumn(ExpandDates, "RunningTotal", (Earlier) => List.Sum(Table.SelectRows(Earlier[Partition], each [Date]<=Earlier[Dates])[Qty])),
AddDaily = Table.AddColumn(AddRT,"Daily", (Earlier) => List.Sum(Table.SelectRows(Earlier[Partition], each [Date]=Earlier[Dates])[Qty])),
Cleanup = Table.RemoveColumns(AddDaily,{"Partition"})
in
Cleanup
Memorable MemberHi ImkeF,
Oh, Both of your solutions and MarcelBeug'solution are interesting to learn. I'm having concern about performance benchmark for your apporach and Marcel's approach, cause i usually think as your nested sum approach for balance.
- ImkeF
Hi tringuyenminh92,
yes, performance is an issue if you do it in M/the query-editor, therefore the suggestion for a measure.
But the Partition-step in my query helps if for whatever reason you want to do it in M ;-)
- tringuyenminh92
Hi ImkeF,
The measure approach is clear to me. Could i have your sample pbix file that you are doing the converting with above M script, so I could quickly observe step by step to understand the role of Partition step? :smileyvery-happy: