cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GZhivkov
Regular Visitor

Vehicle Utilisation (subtracted the Workshop Hours)

Hello community,

 I have to CSV files with one table in each:

- Utilisation

- Workshop

 

My query is about vehicle utilisation, subtracting the time been in the workshop.

 

Table Utilisation looks like this:

VehicleID

DriverID

TripDate

1st Ignition On

1st Ignition Off

Last Ignition On

Last Ignition Off

SH65VFS

James Smith

01/10/2020 00:00

01/10/2020 05:48

01/10/2020 06:05

01/10/2020 11:16

01/10/2020 12:03

SH65VFS

 

01/10/2020 00:00

01/10/2020 06:12

01/10/2020 06:16

01/10/2020 15:00

01/10/2020 15:00

SH65VFS

James Smith

02/10/2020 00:00

02/10/2020 05:45

02/10/2020 07:24

02/10/2020 12:46

02/10/2020 14:28

SH65VFS

 

03/10/2020 00:00

03/10/2020 08:10

03/10/2020 08:10

03/10/2020 08:10

03/10/2020 08:10

SH65VFS

 

07/10/2020 00:00

07/10/2020 17:47

07/10/2020 18:06

07/10/2020 19:25

07/10/2020 19:27

SH65VFS

 

08/10/2020 00:00

08/10/2020 15:44

08/10/2020 15:48

08/10/2020 15:44

08/10/2020 15:48

SH65VFS

 

10/10/2020 00:00

10/10/2020 07:58

10/10/2020 07:59

10/10/2020 08:03

10/10/2020 08:05

 

On the Table Utilsiaiton my query is how to take the total time between 1st Ignition On and Last Ingtion Off (in hours) for the day, regardless if they were multiple drivers or no driver name. It should be able to calculate the toal time for the vehicle from the very 1st ignition On to th last Ignition Off. When I try of course it sums any other time in on the same day but duifferent row (because of no driver name). See above the first two rows.

 

Table Workshop looks like this:

VehicleID

LocationID

DriverID

FromDate

ToDate

SH65VFS

Workshop 1

Beverly Smith

01/10/2020 06:05

01/10/2020 06:12

SH65VFS

Workshop 1

 

01/10/2020 06:16

01/10/2020 06:16

SH65VFS

Workshop 1

Beverly Smith

02/10/2020 14:28

03/10/2020 08:10

SH65VFS

Workshop 1

 

03/10/2020 08:10

07/10/2020 17:47

SH65VFS

Workshop 1

 

10/10/2020 07:59

10/10/2020 08:01

SH65VFS

Workshop 1

 

10/10/2020 08:02

10/10/2020 08:03

SH65VFS

Workshop 1

 

10/10/2020 08:05

12/10/2020 06:35

SH65VFS

Workshop 1

James Smith

12/10/2020 07:36

12/10/2020 07:37

SH65VFS

Workshop 1

James Smith

12/10/2020 07:37

12/10/2020 07:37

 

 I will need to sum any the time for the day (substract FromDate from ToDate column), and count only hours between 7 am and 4 pm.

 

The issue I am having here is that to get Utilisation % I need to divide the vehicle Utilisation hours for the month by my target hours say 160 hours. If the vehicle has been in the workshop between 7am and 4pm I need to substract that from my target hours before I divide the sumn of utilisation hours.

 

Example - Utilisation for the vehicle for the month was total hours between 1st Ignition On and Last Ignition off - 120 hours.

My taget (for 100% utilisation) will be the NETWORKDAYS for that month, for example 160 hours. 

The vehicle has been in the workshop for 3 full days, which will count the time bween 7am abd 4pm (3 days x 9 hrs = 27 hours).

Target is is now 160hrs - 27 = 133.

Utilisation % = 120 hrs / 133 = 90%

 

The issue I am having here is that I need to check if the TripDate (from Utilisation Table) is in between the two dates from Workshop Table - FromDate and ToDate.

 

I tried creating a measure but failed to address the aforementioned issues.

 

 Can someone give me an advice or help please?

1 REPLY 1
BA_Pete
Super User II
Super User II

Hi @GZhivkov ,

 

This should be doable in Power Query initially, then with a couple of measures on top.

Can you use Power Query (i.e. are you using import mode)?

 

If so, can you share your example data as an Excel file or PBIX please? The tables in your OP are not copying over to Power BI properly.

 

Pete

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.