Unexpected results when attempting to locate next matching record using DAX new column

[ditka78]

I have a table of all incoming phone calls.  The table includes a date, answering technician's ID#, answer time, and end of acw time.  Here is a sample of the existing data (there are a number of other columns but they aren't relevant)

Date	ID#	Answer Time	ACW End Time
7/25/2023	85468	3:04:23 PM	3:17:54 PM
7/25/2023	14571	3:05:17 PM	3:30:01 PM
7/25/2023	85468	3:20:13 PM	3:45:05 PM

I am trying to add a new column using DAX.  The new column will display the time the ID# for the row answered their next call. For the last call of the day it will instead display a set time of 11:00:00 PM which will always be after the end of their shift. So the desired result would be:

Date	ID#	Answer Time	ACW End Time	Next Call Answer Time
7/25/2023	85468	3:04:23 PM	3:17:54 PM	3:20:13 PM
7/25/2023	14571	3:05:17 PM	3:30:01 PM	11:00:00 PM
7/25/2023	85468	3:20:13 PM	3:45:05 PM	11:00:00 PM

I utilized the following new column dax code:

Next call start = 
    var agent = 'Call Records'[MMID short]
    var ondate = FORMAT('Call Records'[Date],"m/d/yy")
    var endtime = FORMAT('Call Records'[ACW End Time], "Long Time")
    var nextcalltime = FORMAT(
                MINX(
                TOPN(100,    
                FILTER('Call Records',
                     agent='Call Records'[MMID short]
                     && ondate=FORMAT('Call Records'[Date],"m/d/yy")
                     && endtime <=FORMAT('Call Records'[Answer Time], "Long Time")
                             )
       ,'Call Records'[Answer Time],                      
 ASC
)
,'Call Records'[Answer Time]),"Long Time")

return
IF(ISBLANK(nextcalltime), FORMAT(TIME(23,0,0),"Long Time"),nextcalltime)

At first this appeared to work, but on digging a little I found that it works for SOME rows, but not for others.  There are two issues.  For some combinations of a userID and a date it provides 100% correct results, but for others the first row has the correct response, but then all other rows for the same day show the answer time of the first call of the day.   The second issue is that ISBlank() is not registering the nextcalltime variable as empty so the last call of the day is just blank.  Even if I create an extra new column with a simple isblank() referring to my new column the empty lines don't register as empty.  Attaching some screenshots of actual data

 this day and user ID works.

same day different user ID#  first row is correct, second shows as last of the day and all others show time of the first call.

Thank you for any help.

[ThxAlot]

.

[ditka78 · Accepted solution]

So this didn't work for me, but it got me thinking on the right path.  I was using the format(time, "Long Time") because the column I created prior to this was comparing the time from this table to another table with data pulled from a different system and the time formats weren't matching.  I ended up replacing this with format(time, "Short Time") to force the data into numbers only format and it fixed the issue with some lines not working.  As best I can tell from looking at where the results were incorrect, the <= was comparison was treating both times as text because of the AM/PM despite them being formated as time.  As a result the comparison was being done alphabetically, so 10 AM is < 9 AM and 3 PM < 7 AM.  

The blank lines I found could be filtered with if(nextcall) = ""  in place of if(isblank(nextcall)).  

As an additional note in case anyone is ever trying to figure something out from this in the future, switching to the "Short Time" added an additional wrinkle since it drops the seconds, so I had to also add a variable for starttime and the following to the filter to keep calls shorter than 1 minute from identifying themselves as the next call.

&& starttime <> FORMAT('Call Records'[Answer Time], "Short Time")