Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@Anonymous- 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!
@Anonymous
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.
Hi @Anonymous ,
Can you give me some sample data?
Best Regards,
Icey
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |