IF Statement - recognise if date is within range and identify record

[OpsMgr]

Hi everyone,

I'm very new to DAX and have spent many an hour this week reviewing all of the excellent support this community has offered to others to get me this far... but I'm stuck.

I'm trying to input a flag in my Purchase Orders received data table based on a number of logical arguments, in order to determine if a particular row meets certain criteria;

LATE- the item was received outside of the Late Tolerance & Early Tolerance values as defined by the user viewing the report (these come from two defined parameters within the report). [LastRcpt] is greater than this argument.

EARLY -  the item was received outside of the Late Tolerance & Early Tolerance values as defined by the user viewing the report (these come from two defined parameters within the report). [LastRcpt] is less than this argument.

ODUE - item is not yet delivered but the required date [F_PDDTE (Due Date)] is in the past.

ONTIME - the item was received within the user defined Late Tolerance & Early Tolerance values

NOTDUE - the item is not yet due against [F_PDDTE (Due Date)]

I've attempted to nest these statements so that each value fits within the above arguments. Bot DAX doesn't like this and returns the following error message:

DAX comparison operations do not support comparing values of type Date with values of type True/False. Consider using the VALUE or FORMAT function to convert one of the values.

WIP_OTIF.Flag =

    IF ( AND ( '(HPO)UKCC_PO_LINES'[C_FULFILLED] = "TRUE", '(HPO)UKCC_PO_LINES'[LastRcpt].[Date] > ('(HPO)UKCC_PO_LINES'[F_PDDTE (Due Date)].[Date] + [Late Tolerance Value])), "LATE",

        IF ( AND ( '(HPO)UKCC_PO_LINES'[C_FULFILLED] = "FALSE", TODAY() > '(HPO)UKCC_PO_LINES'[F_PDDTE (Due Date)].[Date]), "ODUE",

            IF ( AND ( '(HPO)UKCC_PO_LINES'[C_FULFILLED] = "TRUE", '(HPO)UKCC_PO_LINES'[LastRcpt].[Date] < ('(HPO)UKCC_PO_LINES'[F_PDDTE (Due Date)].[Date] - [Early Tolerance Value])), "EARLY",

                IF ( AND ( '(HPO)UKCC_PO_LINES'[C_FULFILLED] = "TRUE", '(HPO)UKCC_PO_LINES'[LastRcpt].[Date] = AND( '(HPO)UKCC_PO_LINES'[F_PDDTE (Due Date)].[Date] - [Early Tolerance Value], '(HPO)UKCC_PO_LINES'[F_PDDTE (Due Date)].                    [Date] + [Late Tolerance Value])), "ONTIME",

                        IF ( AND ( '(HPO)UKCC_PO_LINES'[C_FULFILLED] = "FALSE", '(HPO)UKCC_PO_LINES'[F_PDDTE (Due Date)].[Date] > TODAY()), "NOTDUE",

                             "")))))

Key fields:

[F_PDDTE (Due Date)] = the Purchase order requested delivery date - Date Value formatted as Short Date

[LastRcpt] = the last ledger transaction date form another table 

[C_FULFILLED] = TRUE/FALSE on whether a particular PO line has been completely fulfilled (Original qty - qty delivered, 0 = TRUE)

I in many ways need to validate if the PO is fulfilled or not to determine whether an order is ODUE as opposed to LATE...

I'm really stuck with this one - all part of the learning process!

Thanks in advance.

[lukiz84 · Accepted solution]

Hi, try to make it more readable (e.g. using DAX Formatter by SQLBI) then you will notice something. I add it in the comments.

WIP_OTIF.Flag =
IF (
    AND (
        '(HPO)UKCC_PO_LINES'[C_FULFILLED] = "TRUE",
        '(HPO)UKCC_PO_LINES'[LastRcpt].[Date] > ( '(HPO)UKCC_PO_LINES'[F_PDDTE (Due Date)].[Date] + [Late Tolerance Value] )
    ),
    "LATE",
    IF (
        AND (
            '(HPO)UKCC_PO_LINES'[C_FULFILLED] = "FALSE",
            TODAY () > '(HPO)UKCC_PO_LINES'[F_PDDTE (Due Date)].[Date]
        ),
        "ODUE",
        IF (
            AND (
                '(HPO)UKCC_PO_LINES'[C_FULFILLED] = "TRUE",
                '(HPO)UKCC_PO_LINES'[LastRcpt].[Date] < ( '(HPO)UKCC_PO_LINES'[F_PDDTE (Due Date)].[Date] - [Early Tolerance Value] )
            ),
            "EARLY",
            IF (
                AND (
                    '(HPO)UKCC_PO_LINES'[C_FULFILLED] = "TRUE",
                    '(HPO)UKCC_PO_LINES'[LastRcpt].[Date] // HERE IS THE PROBLEM
                        = AND (
                            '(HPO)UKCC_PO_LINES'[F_PDDTE (Due Date)].[Date] - [Early Tolerance Value],
                            '(HPO)UKCC_PO_LINES'[F_PDDTE (Due Date)].[Date] + [Late Tolerance Value]
                        )
                ),
                "ONTIME",
                IF (
                    AND (
                        '(HPO)UKCC_PO_LINES'[C_FULFILLED] = "FALSE",
                        '(HPO)UKCC_PO_LINES'[F_PDDTE (Due Date)].[Date] > TODAY ()
                    ),
                    "NOTDUE",
                    ""
                )
            )
        )
    )
)

See the part where I added // HERE IS THE PROBLEM - in your AND(...) below the comment you just have dates, not booleans, that won't work in an AND() function.

hth and br