Inventory Allocation Based on Open QTY

[adoble0214]

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.

[adoble0214]

Edit: I am unsure how to add the PBIX File to this post....

[v-yueyunzh-msft]

Hi , @adoble0214 

Based on your requirements description, I did not fully understand what the expected output of your detailed requirements is, If you want further help, you need to provide the following things: .pbix files with your sample data, pictures with expected output for the sample data you have provided so far, or Excel files to help us understand the needs, and then place them in a cloud disk such as OneDrive and upload them to this case reply to us, because ordinary users do not have permission to provide files directly at the moment.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly