Determining Safe Miles Driven since last Vehicle Incident

[WxShady13]

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.

 

[etaf]

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]

Blue is the Normal Odometer Readings, Orange is the Vehicle Incident Readings.

 

[etaf]

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

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[WxShady13]

i would use MAX and bring back the Incident date and also the Last od date and mileage
using
a array MAX like
={MAX(IF(($E$2:$E$20=J2),$F$2:$F$20))}

I have attached a sample on dropbox

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Thank you!