Forum Discussion

Nicci's avatar
Nicci
Icon for Helper I rankHelper I
2 years ago
Solved

Get total miles driven in a table

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 dr...
calculation
  • lbendlin's avatar
    lbendlin
    2 years ago

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

     

     

     

  • lbendlin's avatar
    lbendlin
    2 years ago

    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's avatar
Nicci
Icon for Helper I rankHelper I
2 years ago

Hello, Here you go!

Mileage List data:

IDDateVehicle Plate #VehicleIDEnding Mileage
3602/1/2024123451359,098
3612/1/2024123451359,102
3642/2/2024123451359,107
3652/5/2024123451359,112
3662/5/2024123451359,179
3692/5/2024123451359,224
3712/6/2024123451359,307
3952/6/2024123451359,350
3972/6/2024123451359,373
3992/9/2024123451359,437
4012/14/2024123451359,519
4032/14/2024123451359,572
4112/15/2024123451359,665
4132/15/2024123451359,689
4152/15/2024123451359,732
4182/20/2024123451359,805
4202/20/2024123451359,847
4212/21/2024123451359,908
4282/27/2024123451359,986
4312/28/2024123451360,098
4332/28/2024123451360,134
4352/29/2024123451360,289
4362/29/2024123451360,302
4613/4/2024123451360,431
4923/8/2024111119526,317
4933/6/2024111119526,246
5263/18/2024111119526,634.80
5313/18/2024111119526,652.80
5393/15/2024111119526,619
5403/11/2024111119526,391.80
5523/19/2024111119526,728.60

 

 

Vehicle Registration Data:

IDPlate #StartingMileage
131234555637
951111126233

 

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:

IDDateVehicle Plate #VehicleIDEnding MileageTotal Miles
3602/1/2024123451359,0983, 461 (since in the sample there is none before this so we use starting miles in the vehicle reg data)
3612/1/2024123451359,1024
3642/2/2024123451359,1075
3652/5/2024123451359,1125
3662/5/2024123451359,17967


Thank you!

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

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

 

 

 

  • Nicci's avatar
    Nicci
    Icon for Helper I rankHelper I
    2 years ago

    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!

  • Nicci's avatar
    Nicci
    Icon for Helper I rankHelper I
    2 years ago

    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's avatar
    Nicci
    Icon for Helper I rankHelper I
    2 years ago

    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's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

      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's avatar
        Nicci
        Icon for Helper I rankHelper I
        2 years ago

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