Forum Discussion
A seemingly complex task...
What you are describing is quite doable...but you are right, its complex. Would you like to do a video session and look over the steps you need to take ? It would be much easier that going back and forth in email.
Email me [email protected] with a good time to talk and I'll send you a Zoom invitation.
Power BI is easy to learn, but it has hard parts.
|
So
If your orders were in a relationship to your inventory.
And you created a slicer for your orders.
When you selected an order in the slicer.
It would filter your inventory.
If you had your inventory table sorted by pallet id by Lot_no first, which is sequential, and then by FIFO_date, then you could create a "running total" for each product, and you would be able to see how many pallets you needed to pick to hit the number needed by the order.
Then the question is, how do you want to transmit/print the pick list to the warehouse ?Yes, we also need to create a running total measure as well, so that we can compare to ordered quantity.
And yes, I have a relationship between orders and inventory via an item_no master table.
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.
