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
For example, build a diagram, that describes the dynamic of stock of certain sku in certain warehouse. I need to have all this data in a table, not only on visualization.
Cool,
Please provide the formula what u tried and let us know from that formula what u trying get.
That will help us achieve your goal my dear friend.
- MarcelBeug
Community Champion
An advanced Power Query solution below, creating a record for each possible combination SKU/Warehouse/Date with quantities and balances.
It is assumed that your data has unique SKU/Warehouse/Date combinations (i.e. max 1 transaction per SKU / Warehouse / Date), otherwise a grouping step must be added.
If your data set is large, you should expect quite some runtime, but I already managed to reduce runtime by adding helper columns PrevSKU and PrevWarehouse with the values from the previous rows: Power Query is much faster when values on 1 row can be compared with each other, rather than values on different rows,
let Source = Excel.CurrentWorkbook(){[Name="Transactions"]}[Content], Typed1 = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Warehouse", type text}, {"SKU", Int64.Type}, {"Qty", Int64.Type}}), UniqueSKUs = Table.Distinct(Typed1, {"SKU"}), RemovedOthers = Table.SelectColumns(UniqueSKUs,{"SKU"}), WarehouseLists = Table.AddColumn(RemovedOthers, "Warehouse", each List.Distinct(Table.Column(Typed1,"Warehouse"))), ExpandedWarehouse = Table.ExpandListColumn(WarehouseLists, "Warehouse"), DateLists = Table.AddColumn(ExpandedWarehouse, "Date", each List.Distinct(Table.Column(Typed1,"Date"))), ExpandedDate = Table.ExpandListColumn(DateLists, "Date"), Merged = Table.NestedJoin(ExpandedDate,{"SKU", "Warehouse", "Date"},Typed1,{"SKU", "Warehouse", "Date"},"NewColumn",JoinKind.LeftOuter), ExpandedQty = Table.ExpandTableColumn(Merged, "NewColumn", {"Qty"}, {"Qty"}), ReplacedNullsWithZero = Table.ReplaceValue(ExpandedQty,null,0,Replacer.ReplaceValue,{"Qty"}), SortedSKUWHSDate = Table.Sort(ReplacedNullsWithZero,{"SKU", "Warehouse", "Date"}), SKUWarehouse = Table.SelectColumns(SortedSKUWHSDate,{"SKU", "Warehouse"}), PrevSKUWarehouse = Table.InsertRows(SKUWarehouse,0,{[SKU = null,Warehouse = null]}), AddedPrevSKUWarehouse = Table.FromColumns({Table.ToRecords(SortedSKUWHSDate),Table.ToRecords(PrevSKUWarehouse)}), Expanded1 = Table.ExpandRecordColumn(AddedPrevSKUWarehouse, "Column1", {"SKU", "Warehouse", "Date", "Qty"}, {"SKU", "Warehouse", "Date", "Qty"}), Expanded2 = Table.ExpandRecordColumn(Expanded1, "Column2", {"SKU", "Warehouse"}, {"Column2.SKU", "Column2.Warehouse"}), RenamedColumns1 = Table.RenameColumns(Expanded2,{{"Column2.SKU", "PrevSKU"}, {"Column2.Warehouse", "PrevWarehouse"}}), RemovedBottomRow = Table.Buffer(Table.RemoveLastN(RenamedColumns1,1)), Balance = List.Generate( () => [Counter = 0, Balance = RemovedBottomRow[Qty]{0}], each [Counter] < Table.RowCount(RemovedBottomRow), each [Counter = [Counter] + 1, Balance = if RemovedBottomRow[SKU]{Counter} = RemovedBottomRow[PrevSKU]{Counter} and RemovedBottomRow[Warehouse]{Counter} = RemovedBottomRow[PrevWarehouse]{Counter} then [Balance] + RemovedBottomRow[Qty]{Counter} else RemovedBottomRow[Qty]{Counter}], each [Balance]), AddedBalance = Table.FromColumns({Table.ToRecords(RemovedBottomRow),Balance}), Expanded3 = Table.ExpandRecordColumn(AddedBalance, "Column1", {"SKU", "Warehouse", "Date", "Qty", "PrevSKU", "PrevWarehouse"}, {"SKU", "Warehouse", "Date", "Qty", "PrevSKU", "PrevWarehouse"}), RenamedColumns2 = Table.RenameColumns(Expanded3,{{"Column2", "Balance"}}), RemovedColumns = Table.RemoveColumns(RenamedColumns2,{"PrevSKU", "PrevWarehouse"}), Typed2 = Table.TransformColumnTypes(RemovedColumns,{{"Warehouse", type text}, {"Date", type date}, {"Balance", Int64.Type}}) in Typed2- ImkeF
Hi 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- tringuyenminh92
Memorable Member
Hi 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
It looks as if you need a running total (or cumulative total). You should add a Date-Table and then use this for a measure in your data model:
Cumulative Quantity := CALCULATE ( SUM ( Transactions[Qty] ), FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )Your desired report can then be created with a matrix .
For an explanation on how it works, pls check this: http://www.daxpatterns.com/cumulative-total/
Hallo all!
Yes, i need running total, that inlcudes all combinations SKU/Warehouse/Date.
And it's necessary, course i need to visualize a dynamic of stock for all days,
without that gaps, when there was no transactions:
Thanks for the M-code, that's absolutely new for me, and i'll try it:)
But maybe, there's a way to solve the problem thru the measure?
There's ADDMISSINGITEMS function - someone knows how's it works?
