Conditional Merge - Get Availability Date and Purchase order reference for Sales Demand

[Anonymous]

Hi all,

Short introduction

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 for some days now but is stuck on how to move forward.

I would like to do this in PQ as it most likely will be 50k lines or more in the actual data, and in "fear" of potenitally making the end report sluggish I thought PQ was better than DAX...

I am sure this can be done with a conditional merge - not sure if this is best practice though...

Reasoning
Reason for this is that by linking the date and PO reference that is required to cover the demand I can inform the end user of when 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.

The issue

As seen in the picture below, the thought is to look at the demand quanity based on the running total of delivery order or sales order quantity.

I need then to get the date and PO reference at the date when the accumulated PO quantity cover the demand. The last date and PO needed to cover the demand is required, but a list of all POs would be a nice bonus.

I have added a link to the pbix file where I have the running total for all orders and for the purchase orders, but I am struggeling to link these as I must take into regards material, running totals, and the sorting order.

Delivery orders have higher priority than sales orders and if e.g. two delivery orders have the same date, the delivery order with the smallest order number should have priority.

PBIX file

PBIX file (onedrive) 

PBIX file (dropbox) 

If this is solved, hopefully this is something can be useful for others that also work with sales and purchase orders.

The date and PO reference are pushed to the corresponding demand

Example on how the end result could look like.

Regards

Arve

[Anonymous · Accepted solution]

Hi again,

Ok,

I have managed to get quite a bit further and believe I have sold my initial challenge by using a conditional join.
I have added the updated PBIX file for reference, and also below is the m code that solved the issue for me;

= Table.AddColumn(#"Sorted Rows1", "PO Table", each 
//Only applicable for order types "Sales Orders" and "Delivery Orders"
if( [Order Type] = "Sales Order" or [Order Type] = "Delivery Order" and [Running Totals] < 0) then
Table.SelectRows(
RunningTotalsPO, 
//Name of table
(PO) => 
//Conditions
[Material] = PO[Material] and 
([Running Totals] * [StockQtyPrefix]) <= PO[Running Totals] 
)
else null 
)

PBIX Material Availability for SalesOrder - PQ Copy_ver2 

Regards

Arve

[KT_Bsmart2gethe]

Hi @Anonymous ,

I don't seem to be able to download the PBIX file.

Regards

KT

[Anonymous]

Hi @KT_Bsmart2gethe 

My apologies for that!

I have updated the onedrive link and I have also included a dropbox link. 
I trust it work now..

I am not able to verify that they work on this forum due to IT constraintes, but the links did work at my personal laptop.

Regards

Arve