Determining Safe Miles Driven since last Vehicle Incident

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
81
I have an Excel Spreadsheet that has 2 tables on the same worksheet. One table is setup to capture the monthly odometer readings for each truck we have in service (i.e. Truck 660 would show 45,000 one month and then 47,000 next month). I have a Power BI equation to determine how many miles were driven each month for that table. I have a second table designed for Vehicle Incidents which also has an Odometer Reading column. I need to figure out how to determine how many miles have been driven since the last Vehicle Incident. I am using a MAX(Vehicle Incident [Date]) to get the actual last VI date, but I do not know how to calculate the miles driven from another table beyond that date. Thank you for the help.
 

Attachments

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,446
not sure i read your tables correctly
i dont see a reading for the incident

do you use another set of columns each month - whats the difference in the BLUE to ORANGE
 

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
81
Blue is the Normal Odometer Readings, Orange is the Vehicle Incident Readings.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,446
i would use MAX and bring back the Incident date and also the Last od date and mileage
using
an array MAX like this, based on the truck number - if not found will return zero 0
{=MAX(IF(($E$2:$E$20=J2),$F$2:$F$20))}
that matches the Truck number and returns the Date of incident
Truck number in E , Dates in F & odometer in G
{=MAX(IF((E2:E20=J2),G2:G20))}
returns the Odometer for truck

now you can just subtract the 2 odomenter readings

Then repeat for the latest Odometer reading

I have attached a sample on dropbox
 

Forum statistics

Threads
1,085,758
Messages
5,385,720
Members
401,970
Latest member
xPukwe

Some videos you may like

This Week's Hot Topics

Top