Fleet Tracking

[pykie80]

Hi All,

I'm looking for some assistance with a project I'm working on. I have a table that includes Fleet Vehicle IDs and the Groups they belong to, along with a usage report. The (Usage Report) IVMS data provides details such as Vehicle ID, date used, start time, end time, and duration (which I've calculated in seconds), among other information.

Here are the main tasks I'm trying to accomplish:

1. Determine the usage of each vehicle, with the ability to filter by Group, Registration, and Date.
2. Identify concurrent usage, or when vehicles are used at the same time, to assess if groups have more vehicles than needed.
3. This needs to be date focused / even to simplfy timeslot focused

I'm struggling with the large amount of data, as there is a transaction for every second of the day. I've tried grouping the time into hourly slots, which seems manageable.

Does anyone have ideas, layouts, or current templates they could share? Ideally, I'd like to build a fleet dashboard, but the main focus right now is on utilization.

Any help would be greatly appreciated!

[Anonymous]

Hi @pykie80,

I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.

Please continue using Microsoft community forum.

Thank you,
Pavan.

[Anonymous]

Hi @pykie80,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

[rohit1991]

Hi @pykie80

Fleet utilization combined with user accountability is a great way to spot inefficiencies or overlaps in scheduling.

1. Count how many users drove each vehicle
Use a simple DAX measure like this:

UserCount = CALCULATE(DISTINCTCOUNT(Fleet[UserID]), ALLEXCEPT(Fleet, Fleet[VehicleID]))

You can place this in a matrix visual with Vehicle ID on rows to get the per-vehicle count.

2. Detect overlapping usage of the same vehicle
To identify overlaps, you'll need to compare each row's time window against others for the same vehicle.

You can use Power Query to do this:

• Merge the Fleet table to itself on VehicleID

• Then add a custom column with logic like:

(Start1 < End2) and (End1 > Start2)

This helps flag cases where two users' usage windows intersect for the same vehicle. You can add a flag or even list overlapping user pairs.

3. Filter by selected user
Add a slicer on UserID. When applied, the visuals will adjust to show overlaps or usage count for that user only.

Optionally, you can create a custom measure that returns a warning or highlight when the selected user overlaps with someone else for a vehicle.

[Anonymous]

Hi @pykie80,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, kindly "Accept  as  Solution" and give it a 'Kudos' so others can find it easily.

Thank you,
Pavan.

[Anonymous · Accepted solution]

Hi @pykie80,

Thanks for reaching out in Microsoft Community Forum.

Please follow below to resolve the error;

1.Group the IVMS data by time intervals (e.g., hourly or 30-minute slots) to simplify large datasets in Power BI.

2.Use DAX measures to identify overlapping usage periods between vehicles (e.g., by comparing start and end times).

3.Create visuals like stacked bar charts for usage trends, heatmaps for concurrent usage, and a matrix table for time slot-based vehicle tracking with filters for Group, Registration, and Date.

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.

[ryan_mayu]

could you pls proivde some sample data and expected output?