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 ChampionAn 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
Community ChampionIt 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?
- ImkeF
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
- MarcelBeug
Although Imke's previous post was marked as the solution (and I fully agree), just because I would provide more information this evening: below my results of blowing up the transaction table for each combination SKU/WHS/Date applied to Imke's Transactions query in her PBIX-file.
The net result of blowing up the table is that you always have all warehouses and all SKU's in your slicers.
Compared to a previous post I improved performance by creating separate lists for the unique values and by using "combine tables" instead of "merge tables" (and then remove duplicates).
So just for the record (and educational purpose :catwink: ):
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdDBDcAgCAXQXThLI2C0m/Rg3H+NQtSTYOL38vLB2DtkevRwpgYJPtLL8sJIauwZ1olHkTUtKJohTxQXKUDRFGcjXzZuQ3GGbuSgaAtr0DND7zWyPscbWpbp0PED", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Warehouse = _t, SKU = _t, Qty = _t]), 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"}), AddedZeroQty = Table.AddColumn(RemovedOthers, "Qty", each 0, Int64.Type), UniqueWHSs = List.Buffer(List.Distinct(Table.Column(Typed1,"Warehouse"))), WarehouseLists = Table.AddColumn(AddedZeroQty, "Warehouse", each UniqueWHSs, type {text}), ExpandedWarehouse = Table.Buffer(Table.ExpandListColumn(WarehouseLists, "Warehouse")), UniqueDates = List.Buffer(List.Distinct(Table.Column(Typed1,"Date"))), DateLists = Table.AddColumn(ExpandedWarehouse, "Date", each UniqueDates, type {date}), ExpandedDate = Table.Buffer(Table.ExpandListColumn(DateLists, "Date")), CombinedTables = Table.Combine({Typed1, ExpandedDate}), RemovedDuplicates = Table.Distinct(CombinedTables, {"Date", "Warehouse", "SKU"}) in RemovedDuplicates
