Nested If with Multiple And conditions

[Anonymous]

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

[ryan_mayu]

@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 · Accepted solution]

@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]

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 

[Fowmy]

@Anonymous 

Could you explain in words what how do you need to calculate the OverDue and NonOverDue? 

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS ? to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[parry2k]

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

[Anonymous]

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