A seemingly complex task...

[CoreyP]

Hey all,

I've got what might be a fun one for all you experts. Here's what I'm trying to do:

If any of you have a background in warehouse management, you might get this right off the bat. I'm essentially trying to come up with a way to automate a pick sequence. What I want to do, is filter my inventory table to display the pallets that need to be picked, in order, until the ordered quantity is met. 

In the following picture, you can see my example. So, table on the left is from my orders table. It has product and quantity ordered. The table on the right is inventory. When I select the product on the left, I want the table on the right to return all the records up until that black arrow. ( It just happens to be the total of ordered quantity, or 59,400. ) Now, if the ordered quantity were 60,000, I'd want the table to return the next row, even though the total would be 60,840. So, it has to be filtered to AT LEAST the total for ordered quantity, or, all records that exist if we don't have more than ordered. Now, the kicker is this has to be picked in sequence, and we pick by pallet. So, I think whatever happens, I need to be able to rank pallet id by Lot_no first, which is sequential, and then by FIFO_date. This would essentially create a pick sequence by pallet number for each product ordered. ( Note, Inventory and Orders table are connected via 3rd dimTable on Item_no )

I hope I explained this correctly. If not, I can try to build a sample *.pbix with fake data as I'm using sensitive information. Thanks for all the help! =D