Forum Discussion

chasuk19's avatar
chasuk19
Frequent Visitor
4 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.