Forum Discussion

jen8080's avatar
jen8080
Icon for Helper I rankHelper I
5 years ago
Solved

Excel IFS to DAX

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! 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    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.

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    =@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's avatar
      jen8080
      Icon for Helper I rankHelper I
      5 years ago

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

       

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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