Forum Discussion

az12's avatar
az12
New Member
3 years ago
Solved

Time Comparison gives wrong answer

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

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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)

7 Replies

  • az12's avatar
    az12
    New Member
    3 years ago

    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's avatar
      Greg_Deckler
      Community Champion
      3 years ago

      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. 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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)
  • bharath_v's avatar
    bharath_v
    Resolver I
    3 years ago

    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's avatar
      Greg_Deckler
      Community Champion
      3 years ago

      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).

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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: 

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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.