Calculated Column based on date fields - to show Order Status

[Anno2019]

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

[v-diye-msft · Accepted solution]

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

[Anno2019]

Hey Dina

Just a quick question.

How would I be able to use this as a filter.  Currently it works as a calculated measure, but it does not work as a dimension...any thoughts?

[Anno2019]

Worked like magic

Thank you so much!!