Calculating fuel efficiency per row with multiple cars

[Johanno]

Hi! I'm stuck. I have my car details since several years back where I want to calculate fuel efficiency in liters (Volume) per km (Odometer reading) for each row. I have created a new filtered table where I only have fill ups ("Tankning" in Swedish):

If it was only was ONE car I could solve it by creating an index column (1, 2, 3 etc) and then use:

Fuel efficiency = 
Var CurrentOdometer = 'Table tankning only'[Odometer Reading]
Var LastOdometer = LOOKUPVALUE('Table tankning only'[Odometer Reading];'Table tankning only'[Index];'Table tankning only'[Index]-1)
RETURN
    'Table tankning only'[Volume]/(CurrentOdometer-LastOdometer)

However this only works until I reach a new car, then my index reference is wrong. One solution for this is to create one new table per car, but that's not a good looking nor dynamic solution.

Do you have any hints for me to calculate fuel efficiency per row in the table above?

Thank you in advance,

Johan

[Stachu · Accepted solution]

assuming the Odometer Reading for a car can only grow, and you don't fill a car more than once a day this would work:

Fuel efficiency =
VAR CurrentCar = 'Table tankning only'[Bil]
VAR CurrentDate = 'Table tankning only'[Date]
VAR CurrentOdometer = 'Table tankning only'[Odometer Reading]
VAR LastOdometer =
    CALCULATE (
        MAX ( 'Table tankning only'[Odometer Reading] ),
        FILTER (
            'Table tankning only',
            'Table tankning only'[Bil] = CurrentCar
                && 'Table tankning only'[Date] < CurrentDate
        )
    )
RETURN
    DIVIDE ( 'Table tankning only'[Volume], CurrentOdometer - LastOdometer )

if you do fill in single car more than once a day then you can replace the Date filter with Index to get last entry for a given car that's lower than current Index

hope that helps

[Johanno]

Excellent! I'm impressed. Just the kind of solution I wanted, now I have complete control of my car statistics.  I have my data in a Google spreadsheet which I feed using Google Form while filling fuel.

Just need to add a way to put BLANK() the first time a new car appears.

Thank you very much for your help!

/Johan