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 ImkeF
Your approach is much faster indeed. I'll take a closer look this evening. On first sight the result doesn't inlcude all combinations SKU/Warehouse/Date as indicated by OP in the original question.
Otherwise I wonder if that would be really necessary.
Expect another update from me later today.
Thanks,
Marcel
Community ChampionHi tringuyenminh92 and MarcelBeug,
I just remembered a trick I learned from Bill Szysz, that puts the performance here into a different dimension:
let
Source = TransactionTable,
ChgType = Table.TransformColumnTypes(Source,{{"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])))}})),
#"Expanded all" = Table.ExpandTableColumn(Partitions, "all", {"Date", "Qty", "RT"}, {"Date", "Qty", "RT"})
in
#"Expanded all"
In the Partitions: Instead of filtering the table, one creates a list-range. I have absolutely no idea why this is so much faster, but it is :-)
Pls find the workbook here: https://www.dropbox.com/s/02850h7yrqe1kz5/PBI_RunningTotal2_3.xlsx?dl=0
You can adjust the size of the sample data by increasing the numbers in the number in the orange table: