Forum Discussion
Helper IGet total miles driven in a table
Here is one example using a measure. Frankly, this could be a calculated column too.
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]))
Helper II 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.
Super UserYou 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-Forum/ba-p/963216
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/1447523
- 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!