Inventory Allocation Based on Open QTY
Hello All, I hope this all makes sense: I am building a report for a customer, and am working through dynamic inventory allocation to the customers open orders. I have been successfull in allocating the items by their product name, however I am needing help on how to create logic that will pull in the next Sales Order needing fulfilled, once the requested order qty has been completed. For Example: I have 5 different Data sources. 1-3 are original imported data, tabels 4 & 5 are merged/calculated tables. Table 1 is all open orders for the customer (1-Sales Order and 2-Item), what item they have ordered (3-Material), & the qty requested (4-Order QTY in lbs): Table 2 serves as an informational table, providing the different types of material that the customer orders: Table 3 Showcases all the available inventory for the customer, 1-The Material Name, 2-The Serial Number for each item (These are unique to each specific item, and cannot be used more than once), 3-the amount of each item (Lbs): I merged together the Inventory sheet and the open orders to make table 5, with brigning in those serial numbers to the open orders. The Serial number is what the customer uses within their own MRP. The exported data is below. As you can see Sales Order 3170226/4000 was matched by material and pulled in the unique serial numbers successfully. However, the report is allocating all of this materials weight to this one sales order. 3170226/4000's ordered amount is 400,000 lbs, and the merge moved all of the metal in the inventory ( 581,610 lbs) to this order. How do I build a function that merges these together, but then looks at the order QTY and sees that once it is met, the next Sales Order requesting that material is used for allocation. I have attached the PBIX File.
