Error in Comparing Date/Time Columns

[Anonymous]

Can anyone help me with this weird issue that I am facing?

I am trying to define the shift of a person using his In Time data. But the measure classifies a few time instants incorrectly.

The error is only with work shift C.

A, B and Undefined are correct but some instances of them are getting classified as C incorrectly.

The measure for WorkShift is:

WorkShift = 
IF(
    (Sheet1[In Time] > TIME(7,0,0) ) && (Sheet1[In Time] < TIME(9,0,0) ) , "A" , 
    IF (   (Sheet1[In Time] < TIME(17,0,0) ) && (Sheet1[In Time] > TIME(15,0,0) ) , "B" , 
        IF(   (Sheet1[In Time] < TIME(1,0,0) ) || (Sheet1[In Time] > TIME(23,0,0)  ) ,"C" ,"UNDEFINED")
    )
)

The pic shows the error (wrong classification):

https://drive.google.com/file/d/1Tvu3IVCENsI8bm3TrEknerIGIt465oPh/view?usp=sharing

[tex628]

The image link is broken!

[Anonymous]

Yeah, it is not working for some reason. Can you try the google drive link for the same image?

[tex628]

Honestly, if you just use TIME() on your intime column i think it should work 🙂

[Anonymous]

I could not understand it.

Time(,,) is supposed to convert the parameters to a time format. But the In Time column is already in a time format. I need to compare it to my custom Times.

---

@tex628 wrote:

Honestly, if you just use TIME() on your intime column i think it should work 🙂

---

[tex628]

I know it looks weird but TIME() converts to datetime -> 1899-01-01 12:01:01. 

Try and use it as a measure in a card and watch what it produces!

[Anonymous]

Oh! So is there a work around for my requirement?

---

@tex628 wrote:

I know it looks weird but TIME() converts to datetime -> 1899-01-01 12:01:01. 

Try and use it as a measure in a card and watch what it produces!

---

[tex628 · Accepted solution]

Yes, your 'InTime' column is currently time format -> 10:01:01

When you use the TIME() function you will return datetime format -> 1899-01-01 10:01:01

If you create a new column, converting the values that you have in your 'InTime' column using TIME() you should get the same timestamps but in datetime and with 1899-01-01 as the Y/M/D

Use that column instead of the 'InTime' column and the original expression should work.

[Anonymous]

Works! Thank you so much

[tex628]

TIME() Dax syntax converts to datetime:

So your intime column which only consists of a few hours will always be <1 when compared in the measure!