Greetings all, looking for some assistance here. I'm new to Power BI and been given a complex task. Here's the task: find the utilization of certain vehicles, from a certain company, in a certain location, and provide the report in a Daily, monthly, Qtrly scenario (the user will select what date range).
Facts:
- Vehicle company (A, B, C, etc) can have multiple vehicles "checked out". That count goes against their "on Hand" amount, which differs per location.
- There are different Vehicle types per company as well as amounts.
- Vehicles and be checked out different dates per location (i.e. Car from Comp A is out 1/1 to 1/8, while another car could be checked out 1/6-1/10).
- The check out day varies and there are not set limit.
- The as long as the "status" is "OPEN", vehicles checked out will count against "on hands". If status is closed, the vehicle is back in stock/available.
- The return date will be filled based on the customer's request. However, they can go over or under that time, this is where the "status" comes in to resolve that.
| Vehicle type | #of Vehicle used | Company name | Check out | Return | Days used | Location | Total On Hand | Status |
| Car | 2 | A | 1/1/2024 | 1/8/2024 | 7 | Alpha | 10 | Open |
| Truck | 3 | A | 2/15/2024 | 2/23/2024 | 8 | Alpha | 5 | Open |
| Bus | 4 | A | 3/5/2024 | 3/18/2024 | 13 | Alpha | 7 | Close |
| Car | 7 | A | 1/5/2024 | 1/16/2024 | 11 | Bravo | 10 | Close |
| Truck | 3 | A | 1/27/2024 | 2/6/2024 | 10 | Bravo | 5 | Open |
| Bus | 4 | A | 2/5/2024 | 2/7/2024 | 2 | Charlie | 7 | Open |
| Car | 4 | A | 1/31/2027 | 2/7/2024 | 8 | Charlie | 8 | Open |
| Car | 4 | B | 2/16/2024 | 2/25/2024 | 9 | Alpha | 6 | Close |
| Truck | 1 | B | 2/18/2024 | 2/19/2024 | 1 | Alpha | 3 | Open |
| Car | 1 | C | 3/2/2024 | 3/14/2024 | 12 | Delta | 8 | Open |
| Car | 5 | C | 3/5/2024 | 3/14/2024 | 9 | Bravo | 8 | open |
| Truck | 2 | C | 2/22/2024 | 3/1/2024 | 8 | Bravo | 4 | open |
| Trailer | 1 | C | 2/22/2024 | 2/24/2024 | 2 | Bravo | 1 | close |
| Truck | 1 | D | 3/2/2024 | 3/4/2024 | 2 | Charlie | 4 | Open |
| Trailer | 2 | D | 1/19/2024 | 1/31/2024 | 12 | Charlie | 4 | Open |
| BUS | 2 | D | 1/1/2024 | 1/16/2024 | 15 | Charlie | 3 | Open |
I have tried creating a measure that calculates the amount of vehicles given a certain selection from a slice, i.e. date range, company name, vehicle type , etc. This works, but only shows base on selection. I want to be able to show a daily monthly utilization, using a Matrix. With the Matrix, i have done the basic which shows the count for each Company, based on a date, per vehicle type. You can drill down and see the location.
I would like to present the utilization both in percentage and fractional ( i.e 4/10) and this is where i'm stuck. Any help pointing me to the right direction or is this not feasible?
Thanks!
