Conditional Result based on Dates

[GMadd]

I need to create a DAX formula that returns a value based on the dates. I am working with three different dates.

Created On Date = When the delivery was created

Planned Goods Issue Date = When the warehouse should ship the delivery

Shipped Date = When warehouse physically shipped the delivery

We make the assumption that the material was on backorder if the delivery created the same day it was to ship or the day after. 

I need a formula to return a value of:

Back Order - if the delivery was created on  the same day it was to ship and did not ship on the Planned Goods Issue date. (delivery 87980771 in screen shot)

Back Order - if the delivery was created the a day before is was to ship and did not ship on the Planned Goods Issue date. (delivery 87980772 in screen shot)

Back Order - if the delivery was created after the Planned Goods Issue date. (delivery 87980775 in screen shot)

Late - if the delivery was shipped two or more days after the Planned Goods Issue date. (delivery 87980776 in screen shot)

On-Time - if the delivery was created on the same day as the Planned Goods Issue date and was shipped the same day. (delivery 87980773 in screen shot)

On-Time - if the delivery was shipped on or before the Planned Goods Issue date. (deliveries 87980777,87980773,87980778 in screen shot)

Thank you for your help

[Anonymous · Accepted solution]

Hi, @GMadd 

Based on your description, I have created many measures to achieve the effect you are looking for. Following picture shows the effect of the display.

Measure:

Status1 =

VAR _getCreateDate =

    SELECTEDVALUE ( 'Table'[Created On] )

VAR _getPlanedDate =

    SELECTEDVALUE ( 'Table'[Planned Goods Issue] )

VAR _getShippedDate =

    SELECTEDVALUE ( 'Table'[Shipped Date] )

RETURN

    SWITCH (

        TRUE (),

        _getShippedDate - 2 >= _getPlanedDate

            && _getShippedDate - 2 >= _getCreateDate, "Late",

        _getShippedDate >= _getPlanedDate + 1

            || _getShippedDate = _getCreateDate + 1, "Backorder",

        _getShippedDate = _getCreateDate, "On-time",

        "CheckData"

    )

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum