cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SeanTodd Frequent Visitor
Frequent Visitor

Calculate total time at each hour between 2 times

I'm trying to determine the sum of hours at each hour interval between a start time and an end time. I'm using directquery in my BI model. 

 

I have a calendar table for all dates

I have a time table (with start_time, end_time, total_hours)

 

Example record:

start_time  end_time  total_hours

0800          1030         2.5

 

Need to determine this:

0800 - 60 minutes

0900 - 60 minutes

1030 - 30 minutes

 

 

Then, I plan on filtering on hour of day and calculating how many total_hours fall in that range

 

Anyone who can help solve this is a rockstar in my book. 

5 REPLIES 5
Icey New Contributor
New Contributor

Re: Calculate total time at each hour between 2 times

Hi @SeanTodd ,

 

Can you give me some sample data?

 

Best Regards,

Icey

 

james_bi2019 Frequent Visitor
Frequent Visitor

Re: Calculate total time at each hour between 2 times

@SeanTodd- The only way I can think of this at the moment is to write the query in SQL, however as you're using direct query, I'm not sure the query performance will be what you're after.

Essentially, you could use a time dimension and join to your fact table on time.time BETWEEN fact.Start_Time AND fact.End_Time.

This will give you, per fact record, a row for every minute/second (depends on granularity of your time dimension) which you can then group and aggregate as necessary to roll back up to one row per record.

SeanTodd Frequent Visitor
Frequent Visitor

Re: Calculate total time at each hour between 2 times

@james_bi2019  I'd love to hear more about the time dimension table. I already use a calendar table for date comparison, but you're suggesting a seperate time table that breaks out the minutes and sum the minutes via that table that falls in between the start and stop times? Sorry, this part of sql isn't something I've spent a lot of time on. Thank you for your input!

james_bi2019 Frequent Visitor
Frequent Visitor

Re: Calculate total time at each hour between 2 times

@SeanTodd 

I'm not sure of what your final goal is, but this should get you somewhere close:

Yes, a time dimension similar to a date dimension will be very useful for this. One with a row for each minute of a day, so 1440 rows.

Sample Table (Time):

Time_ID  Time      Hour_Int Hour

0              00:00    0             00:00

1              00:01    0             00:00

2              00:02    0             00:00

 

If we take your example table (fact) ignoring the total_hours column

start_time  end_time

0800          1030

0900          0930

 

And write the following SQL statement:

SELECT
	f.Start_Time
	,f.End_Time
	,t.Time_ID
FROM
	Fact f
	LEFT JOIN Time t ON t.Time_ID BETWEEN f.Start_Time AND f.End_Time -1

This will result in a table that returns 150 rows for your first fact record and 30 rows for the second record.

Import this in to Power BI along with a copy of the Time Dimension and create a relationship between Time_ID on both.

You can then use COUNTROWS() in DAX (or any other calculation) and use the Time Dimension to slice as you wish.

If you're after a 30 minute summary, rather than 60, just add a "half_hour" column.

Let me know how you get on.

James

SeanTodd Frequent Visitor
Frequent Visitor

Re: Calculate total time at each hour between 2 times

@james_bi2019  Thank you. I'll work through this. My final goal is to count all time utilization during each hour of the day. I'm trying to determine where our peaks are. 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 146 members 1,630 guests
Please welcome our newest community members: