Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Yuvalokon
Regular Visitor

Time Range

Hi, I wrote the following formula and created a custom column: =if[AccountCreationTime]>=[Terms_Shay.Leads Start Time] and [AccountCreationTime]<=[Terms_Shay.Leads End Time] then 1 else 0 The result I get is that 0:59:45 (first line) is not in the range of hours even though the range is between 17:00:00 and 01:00:00 in the morning.

Thanks for the helpers.

 

 

image.png

5 REPLIES 5
AlB
Community Champion
Community Champion

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

 

SU18_powerbi_badge

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.

 

AlB
Community Champion
Community Champion

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

 

SU18_powerbi_badge

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.

     

Hi @AlB ,

In case when 1:00:00 is for the previous date so how the formula should be?

Example:

Start Time: 17:00:00

End Time : 01:00:00

Actual Time : 00:59:59

AlB
Community Champion
Community Champion

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

SU18_powerbi_badge

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.

 

@AlB 

It still doesn't work for me :

 

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

Yuvalokon_0-1675335925493.png

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors