Forum Discussion
Hourly Duration Chart Help
Hi all, i'm struggling with some data, in which I need to SUM the duration of a list of tasks and spread this duration on a graph/line chart across an hourly basis.
My data set shows the task, it's start and end time and duration. (i also have date, but filtered out for this).
| Task | End Time | Duration | Start Day |
| Task 1 | 23:45:00 | 02:45:00 | Monday |
| Task 1 | 23:55:00 | 02:35:00 | Monday |
| Task 2 | 18:00:00 | 01:15:00 | Tuesday |
I'm trying to sum the time spent across each task per hour (in mins) and display it simarly to below.
Can anyone help please? (apologies if asked before, but i couldnt find anything relevant).
Hi chasuk19 ,
You may try to create a Hours Table first and use CROSSJOIN() to merge two tables.
1.Hours Table
Hours = GENERATESERIES(1,24,1)2. Merge tables:
New Table = FILTER ( CROSSJOIN ( 'Table', 'Hours'), HOUR ('Table'[Start Time] ) <= 'Hours'[Hour of Day] && HOUR ('Table'[End Time] ) >= 'Hours'[Hour of Day] )3.Calculate the duration of each Hour:
duration in hour = IF ( HOUR ( 'New Table'[Start Time] ) = HOUR ('New Table'[End Time] ), MINUTE ('New Table'[End Time] ) - MINUTE ('New Table'[Start Time] ), IF ( HOUR ( 'New Table'[Start Time] ) ='New Table'[Hour of Day], 60 - MINUTE ( 'New Table'[Start Time] ), IF ( HOUR ( 'New Table'[End Time] ) = 'New Table'[Hour of Day], MINUTE ( 'New Table'[End Time]), 60 ) ) )Below is the final output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
After thinking about this more, I think the best way to achieve this is to 'split' out my data into a calculated table and represent the duration as minutes across each hour from each task, like the below. How would I be able to possibly achieve these?
Hi chasuk19 ,
You may try to create a Hours Table first and use CROSSJOIN() to merge two tables.
1.Hours Table
Hours = GENERATESERIES(1,24,1)2. Merge tables:
New Table = FILTER ( CROSSJOIN ( 'Table', 'Hours'), HOUR ('Table'[Start Time] ) <= 'Hours'[Hour of Day] && HOUR ('Table'[End Time] ) >= 'Hours'[Hour of Day] )3.Calculate the duration of each Hour:
duration in hour = IF ( HOUR ( 'New Table'[Start Time] ) = HOUR ('New Table'[End Time] ), MINUTE ('New Table'[End Time] ) - MINUTE ('New Table'[Start Time] ), IF ( HOUR ( 'New Table'[Start Time] ) ='New Table'[Hour of Day], 60 - MINUTE ( 'New Table'[Start Time] ), IF ( HOUR ( 'New Table'[End Time] ) = 'New Table'[Hour of Day], MINUTE ( 'New Table'[End Time]), 60 ) ) )Below is the final output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
