Forum Discussion

kirstenvo's avatar
kirstenvo
Frequent Visitor
3 years ago
Solved

DAX - SWITCH returns incorrect results

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

 

Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    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.

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    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's avatar
      kirstenvo
      Frequent Visitor
      3 years ago

      Hello Johnt75,

       

      This was helpfull! Thank you very much!

       

      Kirsten