Multiple IF AND DAX statement not working

[craigL01]

I have written the following DAX . Its basically looking at 3 date fields on a sql AX database so 01/01/1900 is no date. The first IF is checking there is a date in LETTERSENTDATE01 and there is not a date in LETTERSENTDATE02 result being 1, then the next IF checks there are dates on 01 and 02 and not in 03 result being 2 and the final IF chaecks there are dates in 01, 02 and 03 result being 3.

Visits = IF(PROJTABLE[MXMSURVEYLETTERSENTDATE01]>01/01/1900 && PROJTABLE[MXMSURVEYLETTERSENTDATE02]=01/01/1900,1,

IF(PROJTABLE[MXMSURVEYLETTERSENTDATE01]>01/01/1900 && PROJTABLE[MXMSURVEYLETTERSENTDATE02]>01/01/1900 && PROJTABLE[MXMSURVEYLETTERSENTDATE03]=01/01/1900,2,

IF(PROJTABLE[MXMSURVEYLETTERSENTDATE01]>01/01/1900 && PROJTABLE[MXMSURVEYLETTERSENTDATE02]>01/01/1900 && PROJTABLE[MXMSURVEYLETTERSENTDATE03]>01/01/1900,3,0)))

When it runs it marks evety record with 3 totally ignoring the first two if statements

i did a quick little data table in BI just to show the field results and values and you can see i have lines that should have a visits of 2 and 1.

Appreciate any help you can give

[tamerj1 · Accepted solution]

Hi @craigL01 
You need to use proper date data type

Visits =
IF (
    PROJTABLE[MXMSURVEYLETTERSENTDATE01] > DATE ( 1900, 01, 01 )
        && PROJTABLE[MXMSURVEYLETTERSENTDATE02] = DATE ( 1900, 01, 01 ),
    1,
    IF (
        PROJTABLE[MXMSURVEYLETTERSENTDATE01] > DATE ( 1900, 01, 01 )
            && PROJTABLE[MXMSURVEYLETTERSENTDATE02] > DATE ( 1900, 01, 01 )
            && PROJTABLE[MXMSURVEYLETTERSENTDATE03] = DATE ( 1900, 01, 01 ),
        2,
        IF (
            PROJTABLE[MXMSURVEYLETTERSENTDATE01] > DATE ( 1900, 01, 01 )
                && PROJTABLE[MXMSURVEYLETTERSENTDATE02] > DATE ( 1900, 01, 01 )
                && PROJTABLE[MXMSURVEYLETTERSENTDATE03] > DATE ( 1900, 01, 01 ),
            3,
            0
        )
    )
)

[craigL01]

Brilliant thanks works fine now.