DAX - SWITCH returns incorrect results

[kirstenvo]

I am working with the SWITCH solution for a while now but I have noticed a problem, lines with the same DueDate and ReviewedDate still show "Overdue" as a result.

I am using the following DAX formula:

Overdue? = SWITCH (TRUE(),ISBLANK([ReviewedDate]) && [DueDate] < TODAY(), "Overdue", [DueDate] < [ReviewedDate], "Overdue", [DueDate] = [ReviewedDate], "On Time", "On Time")

As you can see in the screenshot, there is a record (DocumentID 4477) with DueDate and ReviewedDate on same time. The outcome should be "On Time" but it is "Overdue". What am I missing?

Kind regards,

Kirsten

 

[johnt75 · Accepted solution]

The reviewed date has a time part as well as a date part, so the SWITCH is comparing e.g. 9am to midnight - 9am is after midnight so it counts as overdue.

Change the types of the columns to date, not date/time. You need to do that in Power Query as well as in Power BI desktop otherwise they will display as dates only but will still retain the date/time values.

[kirstenvo]

Hello Johnt75,

This was helpfull! Thank you very much!

Kirsten