Time Comparison gives wrong answer

[az12]

Hi,

I'm trying to filter my data (telephone calls events) to only include calls that take place during work hours. Unfortunately the conditional statement I'm using returns the wrong answer from the comparison. Please could anyone help me work out what I'm doing wrong.

The formula to see if the call took place before closing time:

The result of the conditional statement (you can clearly see the calls took place before closing time, so the output should be 1 not 0).

Thank you

[az12]

Thank you @Greg_Deckler - the TRUNC fix worked! I'm not entirely sure why I had to do it that way but applying it did the trick :-).

[Greg_Deckler]

@az12 Great to hear. More than likely, one or both of those columns is actually a date and time column and they are just using a display format of time. That's the most likely cause. 

[Ashish_Mathur]

Hi,

Cannot spot a mistake in the formula.  Ensure that the formula is a calculated column formula (not a measure).  If it still does not help, then share the download link of the PBI file.

[Anonymous]

1. Get rid of the EVENT text in your formula. 

2. Make sure both your time columns are set to the same time format

This worked well for me: 

[bharath_v]

hi @az12 

It works for me with same formula:

Work Hours? = IF(Appointments[Call Time] <= Appointments[Closing time], 1,0)

Hope the time columns have data type as "Time".

[Greg_Deckler]

@az12 @bharath_v My thought here with the formula I provided is that perhaps one or both of the columns is actually a date/time value but being shown as just a time value format. Using TRUNC the way I demonstrated will get rid of the integer portion (# of days since December 30th, 1899) and then you will only be comparing the decimal portions (fraction of a day).

[Greg_Deckler · Accepted solution]

@az12 Can't replicate this. Try this though:

Call During Working Hours = IF( ('EVENT'[Local Start Time] - TRUNC('EVENT'[Local Start Time]) ) <= ('EVENT'[Working Day Closing Time] - TRUNC('EVENT'[Working Day Closing Time]) ),1,0)