Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

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 AM20/11/2017 9:15:00 AM
4/04/2017 6:45:00 AM4/04/2017 7:15:00 AM
11/04/2016 2:30:00 PM11/04/2016 2:45:00 PM
31/07/2017 7:30:00 AM31/07/2017 8:00:00 AM
26/06/2017 11:45:00 AM26/06/2017 12:15:00 PM
30/07/2018 11:45:00 AM30/07/2018 12:15:00 PM
11/09/2017 11:15:00 AM11/09/2017 11:30:00 AM
14/05/2018 10:00:00 AM14/05/2018 10:45:00 AM
19/06/2017 3:30:00 PM19/06/2017 3:45:00 PM
28/08/2018 9:15:00 AM28/08/2018 10:00:00 AM
27/06/2016 8:30:00 AM27/06/2016 9:15:00 AM
20/03/2017 12:15:00 PM20/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

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support

    Hi Anonymous,

     

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

     

    Regards,

    Frank

    • Anonymous's avatar
      Anonymous
      Not applicable

      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.