Timeline dowtime group by hour

[Julver]

Hi, I'm trying to create a chart like below:

The data is similar to:

EQUIP	START TIME	FINISH TIME	CATEGOY COLOR
HT001	1/01/2020 09:00:02	1/01/2020 09:45:00	UNSCHEDULED
HT003	1/01/2020 09:50:01	1/01/2020 14:58:12	BACKLOG
HT004	1/01/2020 12:50:23	1/01/2020 14:50:00	UNSCHEDULED
HT002	1/01/2020 17:18:01	2/01/2020 04:00:09	SCHEDULED

how I can solve this?,  Thank you in advance for any advice!

[Julver]

thanks so much @MFelix , I had  2 results:

A.

B.

Is it possible to show in graph B, fill the same color as the legend of graph A?

[MFelix]

Hi @Julver  when you say to have the same colours what do you mean to have the colours of the no programado and programado based on equipo?

[Julver]

Please, how I can configurate to start the report at 7:00 am and finish 07:00 an from the next day?

[Julver]

exactly, colours of the no programado and programado based on equipo.

[MFelix · Accepted solution]

Hi @Julver ,

First you need to create a calendar table with date time something simlar to this:

DateTime = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ),
        UNION (
            ROW ( "Time", TIME ( 1, 0, 0 ) ),
            ROW ( "Time", TIME ( 2, 0, 0 ) ),
            ROW ( "Time", TIME ( 3, 0, 0 ) ),
            ROW ( "Time", TIME ( 4, 0, 0 ) ),
            ROW ( "Time", TIME ( 5, 0, 0 ) ),
            ROW ( "Time", TIME ( 6, 0, 0 ) ),
            ROW ( "Time", TIME ( 7, 0, 0 ) ),
            ROW ( "Time", TIME ( 9, 0, 0 ) ),
            ROW ( "Time", TIME ( 10, 0, 0 ) ),
            ROW ( "Time", TIME ( 11, 0, 0 ) ),
            ROW ( "Time", TIME ( 12, 0, 0 ) ),
            ROW ( "Time", TIME ( 13, 0, 0 ) ),
            ROW ( "Time", TIME ( 14, 0, 0 ) ),
            ROW ( "Time", TIME ( 15, 0, 0 ) ),
            ROW ( "Time", TIME ( 16, 0, 0 ) ),
            ROW ( "Time", TIME ( 17, 0, 0 ) ),
            ROW ( "Time", TIME ( 18, 0, 0 ) ),
            ROW ( "Time", TIME ( 19, 0, 0 ) ),
            ROW ( "Time", TIME ( 20, 0, 0 ) ),
            ROW ( "Time", TIME ( 21, 0, 0 ) ),
            ROW ( "Time", TIME ( 22, 0, 0 ) ),
            ROW ( "Time", TIME ( 23, 0, 0 ) ),
            ROW ( "Time", TIME ( 24, 0, 0 ) )
        )
    ),
    "DateTime", [Date] + [Time],
    "Hour", HOUR ( [Time] )
)

Now create the following measure:

Count based on hours =
CALCULATE (
    COUNT ( 'Table'[EQUIP] ),
    FILTER (
        'Table',
        'Table'[FINISH TIME] >= MAX ( DateTime[DateTime] )
            && 'Table'[START TIME] <= MAX ( DateTime[DateTime] )
    )
)

This gives the result below using the Date time has an axis and the measure in values:

Looking at your picture seems to me that you are not considering the seconds in each of the start and finish so I created two new columns:

FinishTimeNoSeconds = MROUND ( 'Table'[FINISH TIME], TIME ( 1, 0, 0 ) ) + TIME ( 0, 0, 0 )

StartTimeNoSeconds = MROUND ( 'Table'[START TIME], TIME ( 1, 0, 0 ) ) + TIME ( 0, 0, 0 )

This removes the seconds from the start and finish now you just need to redo your measure to:

Count based on hours no seconds = 
CALCULATE (
    COUNT ( 'Table'[EQUIP] ),
    FILTER (
        'Table',
        'Table'[FinishTimeNoSeconds] >= MAX ( DateTime[DateTime] )
            && 'Table'[StartTimeNoSeconds] <= MAX ( DateTime[DateTime] )
    )
)

Result below:

For the date and hours you need to turn of the concactenate values and show values without data.

Result in attach PBIX file.