Please help building allocation tool in Power BI

[Jhey12345]

I have two tables, Table A (Purchase Details) and Table B (Order Details). I want to allocate the supply qty from Table A to my Table B like in the yellow cells. 

if Demand qty is less than or equal to Supply qty, capture the supply, PO No. and Delivery details, with the earliest delivery date. Since demand qty is less than supply there are still enough supply in PO no. - AA1 that can be allocated to the next Sales Order 2075, and so on and so forth hence, same PO details were populated. On Sales Order 2076, same PO details but delivery date is changed because there will be split shipments coming from supplier from the same PO.

I want the results in Yellow to be populated in Power BI, however, this is too complex and I'm really not able to create a solution. Hope anyone here can give me guidance on how to build this in Power BI. Thank you very much.

[Jhey12345 · Accepted solution]

This is now solved using the solution I found:

Thank you: https://community.fabric.microsoft.com/t5/user/viewprofilepage/user-id/194507

https://community.fabric.microsoft.com/t5/Power-Query/Calculate-The-Date-Where-There-Is-Enough-Suppl...

[amitchandak]

@Jhey12345 , I think you should consider FIFO solution from RADACAD

https://radacad.com/dax-inventory-or-stock-valuation-using-fifo

[Jhey12345]

This did not work for me, but thank you for sharing this.