Hourly Duration Chart Help

[chasuk19]

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

[Anonymous · Accepted solution]

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.

[chasuk19]

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?