Time Range

Hi Yuvalokon 

The problem is that in that case PQ interprets 01:00:00 as being in the same day but you want it to be in the following day. Try:

if [AccountCreationTime] >= [Terms_Shay.Leads Start Time] and [AccountCreationTime] <= [Terms_Shay.Leads End Time] or
([AccountCreationTime] >= [Terms_Shay.Leads Start Time] and [AccountCreationTime] < [Terms_Shay.Leads Start Time] + #duration(1,0,0,0)) then 1 else 0

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Yuvalokon 

It could be updated to work but a much safer and more reasonable option would be to include the dates, so that you have datetimes instead of only times. Otherwise there's ambiguity in the data. For your first row, why does the result have to be 1? You are assuming the 00:59:00 is on the following day and in that case the result would be one but why wouldn't it be in the current day, resulting in a 0? You cannot tell form the data as it currently is.

Including the date would eliminate the ambiguity and your initial code would work.

 

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

Yuvalokon 

The time data type only spans 24h so everything is considered in the same day by PQ. You would need additional data to specify if that is not the case. If the time is on the current date as you say, your initial code should work.

 

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.