Forum Discussion

Nicci's avatar
Nicci
Helper 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 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?

 

 

calculation
  • 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]))

     

10 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

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

      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.