Timeline dowtime group by hour

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.