Excel IFS to DAX

[jen8080]

Hello-

I have this formula in my current excel report 

=@IFS(Y3="","Missing Complete Date",(Y3-W3)<0,"On Time",(Y3-W3)=0,"On Time",(Y3-W3)>0,"Missed")

I need to translate into DAX and keep running into issues.

'Y' = Actual Complete Date

'W'= Target Date

Thank you! 

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

=if(data[actual complete date]=blank(),"Missing Complete Date",if(data[target date]>=data[actual complete date],"On time","Missed"))

Hope this helps.

[mahoneypat]

Not sure if you are looking for a column or measure, but please try this measure expression.  You can adapt for column by removing the aggregation on both - Table[Date1] instead of MIN(Table[Date1])

New Measure =
VAR vDateDiff =
    DATEDIFF (
        MIN ( Table[Date1] ),
        MIN ( Table[Date2] ),
        DAY
    )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( vDateDiff ), "Missing Complete Date",
        vDateDiff >= 0, "On Time",
        vDateDiff < 0, "Missed"
    )

Regards,

Pat

[pranit828]

=@IFS(Y3="","Missing Complete Date",(Y3-W3)<0,"On Time",(Y3-W3)=0,"On Time",(Y3-W3)>0,"Missed")

Hi @jen8080 

Try

SWITCH(TRUE(),
        Y3="","Missing Complete Date",
       (Y3-W3)<0,"On Time",
       (Y3-W3)=0,"On Time",
       (Y3-W3)>0,"Missed")

[jen8080]

Hello- I tried this and get the 'DAC Comparison opertations do not support comparing calues of type Date with Values of type text'.