Get total miles driven in a table

[Nicci]

Hi Everyone,

I have a table in power bi which is rows for each driving trip users enter. They only enter the ending mileage of the car. What I need to do is show in the table how many total miles driven based on taking the ending trip mileage and subtracting it from the last ending trip entered for that car.

Here is the table so far for 1 vehicle. As you can see you can have multiple trips entered on the same day. So if I look at the second one in the list the miles driven should be 22 because 114319 the ending mileage - the ending mileage of the trip before it is 22 miles driven. How would I do this?

[lbendlin · Accepted solution]

Here is one example using a measure. Frankly, this could be a calculated column too.

[lbendlin]

You would not be able to compute that for the first day (3/6/2024).  For all others you would need to assume that trips do never go overnight?

[Nicci]

I have the starting mileage for the vehicle elsewhere so i can calculate that for the first day once I know how to calculate in general for all of these. They do not go overnight, but since you can have multiple trips in a day and the data is in sharepoint I figured instead of using the date since there are duplicates you can use the row ID that sharepoint creates to know the 'last' one. I'm just struggling on how to actually do the calculated DAX to say give me the last one's ending to use as the starting and subtract from the ending of that records.

[lbendlin]

You can sort trips by Ending Mileage (barring any odometer manipulations).

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Nicci]

Hello, Here you go!

Mileage List data:

ID	Date	Vehicle Plate #	VehicleID	Ending Mileage
360	2/1/2024	12345	13	59,098
361	2/1/2024	12345	13	59,102
364	2/2/2024	12345	13	59,107
365	2/5/2024	12345	13	59,112
366	2/5/2024	12345	13	59,179
369	2/5/2024	12345	13	59,224
371	2/6/2024	12345	13	59,307
395	2/6/2024	12345	13	59,350
397	2/6/2024	12345	13	59,373
399	2/9/2024	12345	13	59,437
401	2/14/2024	12345	13	59,519
403	2/14/2024	12345	13	59,572
411	2/15/2024	12345	13	59,665
413	2/15/2024	12345	13	59,689
415	2/15/2024	12345	13	59,732
418	2/20/2024	12345	13	59,805
420	2/20/2024	12345	13	59,847
421	2/21/2024	12345	13	59,908
428	2/27/2024	12345	13	59,986
431	2/28/2024	12345	13	60,098
433	2/28/2024	12345	13	60,134
435	2/29/2024	12345	13	60,289
436	2/29/2024	12345	13	60,302
461	3/4/2024	12345	13	60,431
492	3/8/2024	11111	95	26,317
493	3/6/2024	11111	95	26,246
526	3/18/2024	11111	95	26,634.80
531	3/18/2024	11111	95	26,652.80
539	3/15/2024	11111	95	26,619
540	3/11/2024	11111	95	26,391.80
552	3/19/2024	11111	95	26,728.60

Vehicle Registration Data:

ID	Plate #	StartingMileage
13	12345	55637
95	11111	26233

Expected outcome:

I'm trying to build a table in power bi that lists all the mileage just like the first data list but I also want a column that totals how many miles the driver drove for that trip. Each row in the first table represents a trip they drove with a car. 

For the first record where there is no 'earlier trip' in the data then you should use the 'startingmileage' from the vehicle registration to know the start mileage before any trips were documented, otherwise use the last entry for the vehicle based on vehicle ID.

NOTE: there are multiple months in the real data, so if its 4/1/2024 trip entered and there exists a 3/31/24 record trip use that, otherwise if it doesnt exist at all, use the starting mileage for the very first in the database.

So some math examples for the table should look like this:

ID	Date	Vehicle Plate #	VehicleID	Ending Mileage	Total Miles
360	2/1/2024	12345	13	59,098	3, 461 (since in the sample there is none before this so we use starting miles in the vehicle reg data)
361	2/1/2024	12345	13	59,102	4
364	2/2/2024	12345	13	59,107	5
365	2/5/2024	12345	13	59,112	5
366	2/5/2024	12345	13	59,179	67

Thank you!

[lbendlin · Accepted solution]

Here is one example using a measure. Frankly, this could be a calculated column too.

[Nicci]

To add, I think its something to do with the dates because in my table i'm listing out the 2 trips individually (which is required because different people drove and we cant combine it so need to know person A drove 27 miles on that day and then later person B drove 32 miles, so cant be combined which I think why yours is working if i combined the display dates together.

[lbendlin · Accepted solution]

I think a calculated column can solve a lot of your issues.

Try this:

Eff. Mileage = 
var em = [Ending Mileage]
var veh = [VehicleID]
var pem = maxx(filter(all(Mileage),[VehicleID]=veh && [Ending Mileage]<em),[Ending Mileage])
return em-COALESCE(pem,RELATED(Vehicles[StartingMileage]))

[Nicci]

That did the trick, THANK YOU !!!!!!!!!!!!!!!!

[Nicci]

So I think its close, I am struggling to understand why yours works but when I create a measure and add it to my table its not doing the same thing LOL Here is the crazy numbers its giving me:

The first number 27 is actually correct but thats because its the first and using the starting mileage in the vehicle registration table (yay) but as you can see the numbers just keep multiplying up and up. The second number, 59, is incorrect, it should be 32 and something I found that if you add 32 plus the mileage before (27) it equals 59, but no idea why that is. Is it possible its becuase of the dates being the same so its calculating and adding twice?

Same with the third number, it should be 117, but its giving 172 which is the 117 plus the total before it (59) thats displayed. Aaaaaa LOL

[Nicci]

A calculated column would be ideal then I can do like averages for that vehicle if its a column 🙂 Let me take a look thank you!