Forum Discussion
Calculated Column based on date fields - to show Order Status
Hi PowerBI Guru's
I am hoping someone could help me please, as it seems simple enough but I am unable to build this calculated column.
Below is a screenshot of my data fields and here is the logic I am trying to build.
Logic as follows:
Begin...
if ALL dates are blank then = "Order not yet booked"
if [Booking Date] is not blank but [Pick Up Date] and [Departed Date] and [Arrived Date] and [Clearance Start Date] and [Clearance End Date] and [Delivery Date] is blank then "Order Booked but not picked up"
if [Pick Up Date] is not blank but [Departed Date] and [Arrived Date] and [Clearance Start Date] and [Clearance End Date] and [Delivery Date] is blank then "Order Picked Up but not departed"
if [Departed Date] is not blank but [Arrived Date] and [Clearance Start Date] and [Clearance End Date] and [Delivery Date] is blank then "Order Departed but not arrived"
if [Arrived Date] is not blank but [Clearance Start Date] and [Clearance End Date] and [Delivery Date] is blank then "Order arrived but not started clearance"
if [Clearance Start Date] is not blank but [Clearance End Date] and [Delivery Date] is blank then "Order started clearance but clearance not yet completed"
if [Clearance End Date] is not blank but [Delivery Date] is blank then "Clearance completed but not delivered"
if [Delivery Date] is not blank then "Order delivered"
End...
Data Fields
Hi Anno2019 ,
Please use the measure below:
Measure = IF ( NOT ( ISBLANK ( MAX ( Table1[Booked Date] ) ) ) && ISBLANK(MAX([Arrived Date]))&&ISBLANK(MAX([Clearance End Date]))&&ISBLANK(MAX([Clearance Start Date]))&&ISBLANK(MAX([Delivery Date]))&&ISBLANK(MAX([Departed Date]))&&ISBLANK(MAX([Pick Up Date])), "Order Booked but not picked up", IF ( NOT ( ISBLANK ( MAX ( Table1[Pick Up Date] ) ) ) && ISBLANK(MAX([Arrived Date]))&&ISBLANK(MAX([Clearance End Date]))&&ISBLANK(MAX([Clearance Start Date]))&&ISBLANK(MAX([Delivery Date]))&&ISBLANK(MAX([Departed Date])), "Order Picked Up but not departed", IF ( NOT ( ISBLANK ( MAX ( Table1[Departed Date] ) ) ) &&ISBLANK(MAX([Arrived Date]))&&ISBLANK(MAX([Clearance End Date]))&&ISBLANK(MAX([Clearance Start Date]))&&ISBLANK(MAX([Delivery Date])), "Order Departed but not arrived", IF ( NOT ( ISBLANK ( MAX ( Table1[Arrived Date] ) ) ) &&ISBLANK(MAX([Clearance End Date]))&&ISBLANK(MAX([Clearance Start Date]))&&ISBLANK(MAX([Delivery Date])), "Order arrived but not started clearance", IF ( NOT ( ISBLANK ( MAX ( Table1[Clearance Start Date] ) ) ) &&ISBLANK(MAX([Clearance End Date]))&&ISBLANK(MAX([Delivery Date])), "Order started clearance but clearance not yet completed", IF ( NOT ( ISBLANK ( MAX ( Table1[Clearance End Date] ) ) ) && MAX( Table1[Delivery Date] ) = BLANK (), "Clearance completed but not delivered", IF ( NOT ( ISBLANK ( MAX ( Table1[Delivery Date] ) ) ), "Order delivered", "Order not yet booked" ) ) ) ) ) ) )
Best regards,
Dina Ye
3 Replies
Hi Anno2019 ,
Please use the measure below:
Measure = IF ( NOT ( ISBLANK ( MAX ( Table1[Booked Date] ) ) ) && ISBLANK(MAX([Arrived Date]))&&ISBLANK(MAX([Clearance End Date]))&&ISBLANK(MAX([Clearance Start Date]))&&ISBLANK(MAX([Delivery Date]))&&ISBLANK(MAX([Departed Date]))&&ISBLANK(MAX([Pick Up Date])), "Order Booked but not picked up", IF ( NOT ( ISBLANK ( MAX ( Table1[Pick Up Date] ) ) ) && ISBLANK(MAX([Arrived Date]))&&ISBLANK(MAX([Clearance End Date]))&&ISBLANK(MAX([Clearance Start Date]))&&ISBLANK(MAX([Delivery Date]))&&ISBLANK(MAX([Departed Date])), "Order Picked Up but not departed", IF ( NOT ( ISBLANK ( MAX ( Table1[Departed Date] ) ) ) &&ISBLANK(MAX([Arrived Date]))&&ISBLANK(MAX([Clearance End Date]))&&ISBLANK(MAX([Clearance Start Date]))&&ISBLANK(MAX([Delivery Date])), "Order Departed but not arrived", IF ( NOT ( ISBLANK ( MAX ( Table1[Arrived Date] ) ) ) &&ISBLANK(MAX([Clearance End Date]))&&ISBLANK(MAX([Clearance Start Date]))&&ISBLANK(MAX([Delivery Date])), "Order arrived but not started clearance", IF ( NOT ( ISBLANK ( MAX ( Table1[Clearance Start Date] ) ) ) &&ISBLANK(MAX([Clearance End Date]))&&ISBLANK(MAX([Delivery Date])), "Order started clearance but clearance not yet completed", IF ( NOT ( ISBLANK ( MAX ( Table1[Clearance End Date] ) ) ) && MAX( Table1[Delivery Date] ) = BLANK (), "Clearance completed but not delivered", IF ( NOT ( ISBLANK ( MAX ( Table1[Delivery Date] ) ) ), "Order delivered", "Order not yet booked" ) ) ) ) ) ) )
Best regards,
Dina Ye
