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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SeanTodd
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
james_bi2019
Frequent Visitor

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

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

@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

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

Icey
Community Support
Community Support

Hi @SeanTodd ,

 

Can you give me some sample data?

 

Best Regards,

Icey

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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