Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Nested If with Multiple And conditions

HI,

 

So im trying to get a Calculated column which returns  Overdue or Not overdue based on certain criteria

First time poster so please forgive any errors in required  format 

 

Below is what i came up with

Overdue_VW =
IF(And([Status]="Completed",[CompleteBy]<[CompletedDate]),"Overdue",
if(and([Status]<>"Completed",And([CompletedDate]="",And([CompleteBy]<=DATE(YEAR(TODAY()),MONTH(TODAY()),1),And([Status]<>"Deleted")))),"Overdue","Not Overdue"))

 

I also tried  ( although im not familiar with the switch expression, first time ive tried to use it)

 

Overdue_VW =
IF(And([Status]="Completed",[CompleteBy]<[CompletedDate]),"Overdue",
Switch(
[Status]<>"Completed",
[CompletedDate]="",
[CompleteBy]<=DATE(YEAR(TODAY()),MONTH(TODAY()),1),
[Status]<>"Deleted"),
"Overdue", "Not Overdue"))
 
I think im close, but cant quite work out why either option isnt working,  Any help on this would be greatly appreciated
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous change you expression like this, i just copied your expression assuming tht is the business logic you want.

     

    Overdue_VW = 
IF([Status]="Completed" && [CompleteBy]<[CompletedDate],"Overdue",
IF(
[Status]<>"Completed" &&
[CompletedDate]=BLANK() &&
[CompleteBy]<=DATE(YEAR(TODAY()),MONTH(TODAY()),1) &&
[Status]<>"Deleted",
"Overdue", "Not Overdue"))

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

6 Replies

    • parry2k's avatar
      parry2k
      Super User
      6 years ago

      Anonymous post a sample data and business logic to calculate this new column.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi

         

        Sorry took me a while to work out how to get a sample set of data on here, Not totally sure this will work either 

         

        Test PBI file 

         

         

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    6 years ago

    Anonymous 

    when you have serveral conditions of AND logic, please use && instead of AND

    e.g and(condition1,and(condition2,condition3))

    you can try condition1 && condition2 && condition3

    That will be easier.

    • parry2k's avatar
      parry2k
      Super User
      6 years ago

      Anonymous change you expression like this, i just copied your expression assuming tht is the business logic you want.

       

      Overdue_VW = 
IF([Status]="Completed" && [CompleteBy]<[CompletedDate],"Overdue",
IF(
[Status]<>"Completed" &&
[CompletedDate]=BLANK() &&
[CompleteBy]<=DATE(YEAR(TODAY()),MONTH(TODAY()),1) &&
[Status]<>"Deleted",
"Overdue", "Not Overdue"))

       

      I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

      ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thank you, this drove me nuts all day

         

        i can see i was mixing an AND  with &&  which was over complicating things

         

        really appreciate your time and the time of everyone who answered