Date Range In Power Bi between Start / end date with working hours

[Anonymous]

Hi there,

I want to create a Heat Matrix that shows you the active users/vehicles that are out and about based on their working hours - So if one vehicle is out from 1am - 5am on a monday it should show as 
       Mon

1am    1

2am    1

3am     1

4am     1

5am     1

however if 2 vehicles are out from 1am to 5am on a monday it should show as 2 and so on

        mon

1am    2

2am    2

3am     2

4am     2

5am     2

Currently my data is Australia Time Start UTC+10Australia Time End UTC+10

20/11/2017 8:30:00 AM	20/11/2017 9:15:00 AM
4/04/2017 6:45:00 AM	4/04/2017 7:15:00 AM
11/04/2016 2:30:00 PM	11/04/2016 2:45:00 PM
31/07/2017 7:30:00 AM	31/07/2017 8:00:00 AM
26/06/2017 11:45:00 AM	26/06/2017 12:15:00 PM
30/07/2018 11:45:00 AM	30/07/2018 12:15:00 PM
11/09/2017 11:15:00 AM	11/09/2017 11:30:00 AM
14/05/2018 10:00:00 AM	14/05/2018 10:45:00 AM
19/06/2017 3:30:00 PM	19/06/2017 3:45:00 PM
28/08/2018 9:15:00 AM	28/08/2018 10:00:00 AM
27/06/2016 8:30:00 AM	27/06/2016 9:15:00 AM
20/03/2017 12:15:00 PM	20/03/2017 12:30:00 PM

also right next to these columns is a Vehicle Column.

My end goal is to produce something like this --- which is the total working hours per vehicle

Thank you for helping

[v-frfei-msft]

Hi @Anonymous,

Based on my test, it could not be achieved by power BI using the sample data as you shared.

Regards,

Frank

[Anonymous]

Hi Frank, 

Maybe this SQL query can help - 

I believe it is to do with the Query between start and end date - but i'm not sure on how to do this via Power Bi, Also i'm using a Postgresql database if that helps.

Cheers Frank.