Finding value of previous row only if another valu...

Rogerio_Penna · 12-05-2019 10:06 AM

I have this car fuel consumption app.
I have cars registered in a table.

And the fueling table has a “belongs to” the car table.

So when you register a new refueling, you fill the odometer’s kilometrage or mileage.

And how much fuel you filled in the car. Price, etc.

I want to calculate the current trip (since last fueling) kilometrage. So I need to calculate the current kilometrage minus the kilometrage from the previous fueling.

Remember that this table is “a part of”. So I must get the last kilometrage only in the rows where the car is the same as the one in the current row we are filling.

Consider the column for the odometer is called KM, the difference between last two kilometrages is “KM TRIP” and there is also a column called CAR.

So I might have something like this

CAR--------------KM----------TRIP
Civic 2015…65000…?
Civic 2015…65500…?
Passat 2011…43000…?
Civic 2015…66000… calculation is obviously 66000 - 65500. But how to get the 65500?

ps: there is also a timestamp column if it’s needed to find the last row…

Billy_Sheng

Can you elaborate on this part:

So when you register a new refueling, you fill the odometer’s kilometrage or mileage.
And how much fuel you filled in the car. Price, etc.

I’m not quite understanding the question.

LeventK

@Rogerio_Penna
Just construct a Virtual Column with using MAXROW(…) expression which will return the last row’s key column value of the [Related Mileages] list for each particular car and you can then use a de-ref expression to retrieve the [KM] value with [YourVirtualColumnName].[KM]

//Name the Virtual Column as you like i.e. [Mileage_Info]
MAXROW("Fueling", "Timestamp_Column_Name_Here", ([Car] = [_THISROW].[Car]))

Then you can use a de-ref to retrieve the [KM] value like this:

[Mileage_Info].[KM]

Finding value of previous row only if another value matches