Forum Discussion

Bish92's avatar
Bish92
Frequent Visitor
4 years ago
Solved

Multiple If Statements

Hi, I need help converting an Excel Formula for it to work in Powerbi... The statement is the following...

=IF([@[PROMISE_DATE]]="","On Time", IF(AND([@[COMMIT TO SHIP]]<TODAY(), [@[PROMISE_DATE]]<TODAY()),"Late", IF([@[COMMIT TO SHIP]]<=[@[PROMISE_DATE]],"On Time","Late")))

 

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Bish92 

    You can try this, change the part in the rectangle

    On Time/Late = IF('Table'[PROMISE_DATE]=BLANK(),"On Time",IF('Table'[COMMIT TO SHIP]<TODAY() && 'Table'[PROMISE_DATE]<TODAY(),"Late", IF('Table'[COMMIT TO SHIP]<='Table'[PROMISE_DATE],"On Time","Late")))

    FYI: DAX operators - DAX | Microsoft Docs

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi Bish92 

    You can try this, change the part in the rectangle

    On Time/Late = IF('Table'[PROMISE_DATE]=BLANK(),"On Time",IF('Table'[COMMIT TO SHIP]<TODAY() && 'Table'[PROMISE_DATE]<TODAY(),"Late", IF('Table'[COMMIT TO SHIP]<='Table'[PROMISE_DATE],"On Time","Late")))

    FYI: DAX operators - DAX | Microsoft Docs

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    Welcome to the forum.

    Are you trying to create a calculated column?

    If so, what errors are problems have you received?

  • Bish92's avatar
    Bish92
    Frequent Visitor
    4 years ago

    Hi, Thank you for replying!

    The main issue i am having is..

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

     

     

    • Shishir22's avatar
      Shishir22
      Solution Sage
      4 years ago

      Hello Bish92 .

       

      Please change datatype of date feilds to Date.

       

      Please mark it answer if it resolves your isseue. Kudos are also appreciated.

       

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    It's easier to debug an IF statement by breaking it down into separate clauses - so you can identify which part is throwing the error.

    For now, let's test out the first clause by creating a test column:

    IF([@[PROMISE_DATE]]="","On Time", "Late")

    I think that will probably throw an error, because a date is being compared to a string, You can test a date against BLANK() instead.

    Let me know how it goes

  • Bish92's avatar
    Bish92
    Frequent Visitor
    4 years ago

    Hi All,

    Thanks to everyone who responded. I have managed to fix it and get it working!

    I appreciate the help.