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
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?
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
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 RemovedDuplicatesHi MarcelBeug,
yes, this is very nice code. Much faster than my attempts with Joins. So my M-winner of the day is this combined code :
let Source = TransactionTable, 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"}), ChgType = Table.TransformColumnTypes(RemovedDuplicates ,{{"Date", type date}}), Sort = Table.Buffer(Table.Sort(ChgType,{{"SKU", Order.Ascending}, {"Warehouse", Order.Ascending}, {"Date", Order.Ascending}})), Partitions = Table.Buffer(Table.Group(Sort, {"Warehouse", "SKU"}, {{"all", (Earlier) => Table.AddColumn(Table.AddIndexColumn(Earlier, "Index",1,1), "RT", each List.Sum(List.Range(Earlier[Qty],0,[Index])))}})), ExpandAll = Table.ExpandTableColumn(Partitions, "all", {"Date", "Qty", "RT"}, {"Date", "Qty", "RT"}) in ExpandAllBetter together :-)
Hi Tiolan,
this is a very common error. One should always hide the fact-table's date columns from view. Or better: Hide the fact-tables completely. (And put your measures into "blank-measure-only"-tables).
You can use the Enter-data-feature to copy-paste tables from Excel or wherever: http://powerbi.tips/2016/04/manually-enter-data/
Thak you!
it was exacly my mistake!
Tell me please, how did you include the data in .pbix file?
