Forum Discussion

delsbg's avatar
delsbg
Helper I
5 years ago

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 compar...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.