How to group by hours?

[briano93]

Hi all,

I am exploring bike share data. I have over 27000 bike journeys for 2019. I would like to group these journeys into each hour of 2019. There are 8760 hours in a year. I would like a count of each bike journey for each hour of 2019.

In the image above, for example, we can see that on 01/01/2019, between the hours of 02:00 and 03:00 there were 0 bike journeys. In that same day between 13:00 and 14:00, we see that there were 8 journeys. 

It would be nice if I had something that looked like the image above, with the count of bike journeys beside it. I would like to do this for the whole year.

Any help would be greatly appreciated!

[Syndicate_Admin]

This is the best answer you solved me the day Thanks a hug

[v-rzhou-msft · Accepted solution]

Hi @briano93 

According to your statement, you want to count journeys between two whole datetime hour range. I think you can try to create a calculated table with RANGESTART and RANGEEND, then count journeys.

Firstly enter a time table from 0:00:00-23:00:00.

Then create a calculated table to show result.

DateTime = 
VAR _T1 =
    ADDCOLUMNS (
        CROSSJOIN ( CALENDAR ( DATE ( 2019, 01, 01 ), DATE ( 2019, 01, 01 ) ), 'Time' ),
        "RANGESTART", [Date] + [Time]
    )
VAR _T2 =
    SUMMARIZE (
        _T1,
        [Date],
        [RANGESTART],
        "RANGEND", [RANGESTART] + TIME ( 1, 0, 0 )
    )
VAR _RESULT =
    ADDCOLUMNS (
        _T2,
        "COUNT JOURNEYS",
            CALCULATE (
                COUNT ( 'Table'[ID] ),
                FILTER (
                    'Table',
                    AND (
                        'Table'[DateTime] >= EARLIER ( [RANGESTART] ),
                        'Table'[DateTime] < EARLIER ( [RANGEND] )
                    )
                )
            ) + 0
    )
RETURN
    _RESULT

Result is as below. Here I use the same data sample like yours. On 2019/01/01 between 13:00 and 14:00, we see that there were 8 journeys. 

You can download my sample file to get more details.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01]

@briano93 can you provide some sample data in a table format

[briano93]

Hi,

Apologies, yes of course. Here are all the journeys for the 1st January 2019:

DateTime		ID
01/01/2019 00:17		1754125
01/01/2019 07:55		1754170
01/01/2019 11:42		1754209
01/01/2019 11:50		1754211
01/01/2019 12:11		1754224
01/01/2019 12:15		1754225
01/01/2019 12:26		1754232
01/01/2019 12:59		1754252
01/01/2019 13:20		1754259
01/01/2019 13:24		1754261
01/01/2019 13:25		1754262
01/01/2019 13:26		1754264
01/01/2019 13:28		1754266
01/01/2019 13:33		1754271
01/01/2019 13:33		1754272
01/01/2019 13:46		1754278
01/01/2019 14:03		1754283
01/01/2019 14:03		1754282
01/01/2019 14:05		1754285
01/01/2019 14:42		1754313
01/01/2019 14:53		1754320
01/01/2019 14:54		1754324
01/01/2019 14:54		1754323
01/01/2019 15:11		1754334
01/01/2019 15:19		1754343
01/01/2019 17:00		1754403
01/01/2019 17:03		1754404
01/01/2019 17:48		1754428
01/01/2019 17:58		1754432
01/01/2019 18:21		1754446
01/01/2019 19:27		1754465
01/01/2019 20:03		1754482
01/01/2019 21:26		1754496
01/01/2019 21:27		1754497

Sorry about the formatting

[AlexisOlson]

I'd recommend keeping the DateTime column and adding a new custom DateHour column:

Time.StartOfHour([DateTime])