Hi all,
I am on a quest to find the date and purchase order reference that is required to cover a demand, if the cumulative inventory level is negative.
I made this in excel, vba, years back, but have been twisting my head all day and not moved an inch on how to do this in dax (or PQ for that matter).
As seen in the picture below, the thought is to look at the demand (delivery order or sales order) and sum the purchase orders (inventory on order) required until it covers the cumulative inventory for that demand.
I need to link the max date and PO reference that is required to cover the demand to the demand to ensure that the end user know when it is expected that the demand is fulfilled, and which PO he/she might have to chase up if they would like to expedite the order.
This should iterate through every demand, if cumulative inventory is negative.
Example on how the end result could look like.
Attached is the PBIX file that I have made so far.
Note that the model and queries are simplified, but in general similar to how the excel files from SAP.
I have made a running total in PQ that is currently not in use in regards to DAX and I have added a “target end state” table for reference to hopefully make it easier to see what I am aiming for.
Appreciate any assistance and suggestions that you may have 
Regards
Arve
Material Availability for SalesOrder.pbix (330.8 KB)