Forum Discussion
A seemingly complex task...
That would be extremely helpful, however, I'm limited in sharing data since it's all confidential information.
Do you mind if we just go step by step here?
I think the first thing we need to do is create a measure for Pallet Pick Sequence, which would be a RANKX measure of Pallet_ID, ranked first by minimum value in LOT_NO, then minimum value in FIFO_DATE. Correct?
I don't know if you need a ranking... i think just sorting the table would give you want you want. What you are going to need is some sort of index field to use in doing the running total. It's possible you could use the ranking as an index to step to the "next" pallet when you are doing the running total.
**edit**
In researching cumulative totals, I do see we would need an index column. And yes, for this situation we would use the Pallet Rank as our index column.
A measure will need some way of calculating its "previous" row, as there is no built in concept of row numbers in power bi. Sometimes people use dates for this, often they use an index column.
An index column would imply that all your pallets were sorted in the correct order.
Then your measure can do a sum() of all the rows with the index > the row it is executing on
which sort of implies that you have filtered the records to contain pallets with that part
I created a "Pallet Ranking" by going into query editor, and sorting first by SKU, then LOT, then FIFO date, and adding an Index column.
Seems to work. So how would I create the running total measure? I tried the following but it did not work:
Cumulative Qty =CALCULATE (SUM( 'Inventory'[ONHAND_QTY] ),FILTER (ALL ( 'Inventory'[Pallet Rank] ),'Inventory'[Pallet Rank] <= MAX ( ( 'Inventory'[Pallet Rank] ) )))