Forum Discussion
Complicated date matching help needed...
We have three tables that need to be compared to determine what, if any action needs to be taken to make sure we have enough inventory on hand to service custoemr orders. Ultimately I need to compare the date that we NEED material in-house to the date that it is scheduled to be delivered by our vendors and determine if we need to pull-in, push-out or cancel each order with the vendors. I've attached a screen shot of the the three tables and the desired output.
4 Replies
Hi delsbg ,
Here are the steps you can follow：
1. Create calculated colum.
New_Due_Date = IF('PURCHASE_ORDERS'[Part_ID]=RELATED('CUSTOMER_ORDERS'[Part_ID]) &&'PURCHASE_ORDERS'[QTY]>=RELATED('parts_on_hand'[On Hand])&& RELATED('CUSTOMER_ORDERS'[QTYReqd])<>RELATED('parts_on_hand'[On Hand]) , CALCULATE(MAX('CUSTOMER_ORDERS'[Due_Date]),FILTER('CUSTOMER_ORDERS','CUSTOMER_ORDERS'[Part_ID]=EARLIER('PURCHASE_ORDERS'[Part_ID]))) , BLANK())Action = SWITCH( TRUE(), 'PURCHASE_ORDERS'[New_Due_Date]=BLANK(),"Cancel - no demand", 'PURCHASE_ORDERS'[New_Due_Date]<'PURCHASE_ORDERS'[Due_Date],"Pull in", 'PURCHASE_ORDERS'[New_Due_Date]>'PURCHASE_ORDERS'[Due_Date],"Push out" )2. Result
You can downloaded PBIX file from here
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Liu for the solution. I tried to implement it and the issue I am running into is this: In reality I have many orders and many purchase orders for the various part Id's, and when I tried to use your formula and dug into the .pbix file, your example is built from a relationship of 1:1 between customer orders and parts on hand. In reality it needs to be a many to one relationship. What I've done so far is created a UNION table with QOH, customer orders and purchases orders with a running total for each part with QOH as the opening balance (I give a date to the opening balance of 01/01/2010 so it is always first) and then the customers orders are included as negatives and purchase order receipts as positives, each with their dates. Then I create a column with a running total by type. Any time a negative occurs it means a shortage (the most critical curcumstance this report needs to address- or pull-ins). I somehow need a way for each negative to be matched up with the next Purchase Order for that part and have the new date required matched somehow with that PO so we can send it to the supplier.
Having said all that, I appreciate your solution. It is very impressive. It seems however to only handle a situation where there is a 1:1 between orders and parts.
Hi delsbg ,
Sorry, because of the example, I thought it was a one-to-one relationship...
Can you share your pbix document? By the way, let me tell you the logic of the two columns New_Due_Date and Action, so as to better solve your problem.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Here is a pbix file that contains exactly what your original file contained except I've added in multiple orders for the same part number. Doing so returns an error message for your two new columns.
The file is here
I'm not sure I understand your question regarding the logic of the New_Due_Date and Action column. They seem to be working in your example. The only issue is the fact that your model does not support multiple customer orders for the same part. In reality this report needs to make sure we adjust due dates on Purchase Orders so that we have product in house on the day we need it. It should also make sure we don't have it early.
