Overdue based on multiple Columns

[ZachUnger]

Hi Team 

I'm trying to create a calculated column based on the below logic and wondering if anyone can help? 

The new Column will be the overdue in yellow. 

Any help is appreciated. 

Cheers 

Zach

[ZachUnger]

Thanks, Shravan133 this calculation is just showing past for everything... 

[Irwan]

hello @ZachUnger 

does SWITCH plausible to be used for your goal?

Overdue =
SWITCH(
    TRUE(),
    'Table'[Date info sent]<>BLANK()&&'Table'[Date info sent]>TODAY(),"No",
    'Table'[Required by]>TODAY(),"No",
    'Table'[Extended to]<TODAY(),"Yes",
    'Table'[Required by]<TODAY()&&'Table'[Extended to]>TODAY(),"No",
    'Table'[Required by]<TODAY()&&'Table'[Extended to]<TODAY(),"Yes"
)

Hope this will help.

Thank you.

[ZachUnger]

Hi Irwan 

This is working except for if date info sent, if this column has a date shouldn't show as overdue. May be asking it to do too much...

[Irwan · Accepted solution]

hello @ZachUnger 

as far as i know, SWITCH() works similarly like IF(), so it will process the first line then second line and so on.

that happens because you have date info sent value less than today (initially this what i thought but looks like you want to have "No" if there is value in date info sent not matter what the value is).

Try removing this line or line 5 in your DAX, it should fix the issue.

Overdue =
SWITCH(
    TRUE(),
    'Table'[Date info sent]<>BLANK()&&'Table'[Date info sent]>TODAY(),"No",
    'Table'[Required by]>TODAY(),"No",
    'Table'[Extended to]<TODAY(),"Yes",
    'Table'[Required by]<TODAY()&&'Table'[Extended to]>TODAY(),"No",
    'Table'[Required by]<TODAY()&&'Table'[Extended to]<TODAY(),"Yes"
)

Hope this will help.

Thank you.

[ZachUnger]

Perfecto! Thanking you kindly! 

[Irwan]

hello @ZachUnger 

glad to be a help.

Thank you.

[Shravan133]

you can create a conditional column or measure like this:

DateStatusMeasure =
IF(
ISBLANK(MAX(TableName[DateField])),
"No Date",
IF(
MAX(TableName[DateField]) < TODAY(),
"Past",
"Future"
)
)

modify it according to your needs