Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
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
Proud to be a Datanaut!
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |