Previous registration to get the difference

[Luis_Rodriguez]

I have a table with the fields Date, Machine Code, Hour Meter and Number of Gallons shipped; I need to calculate the average consumption between each fuel dispatch, subtracting the current hourmeter minus the previous hourmeter; I have more than 6 thousand records and I get the current record with MAX (hour meter), but I don’t know how to get the previous hour meter, how can I do the query with SELECT ().

[Aleksi]

You can read the previous hour meter for example like…
LOOKUP(MAXROW(“TableName”,“Date”,[Machine Code]=[_THISROW].[Machine Code]),“TableName”,“KeyColumn”,“Hour Meter”)

[Luis_Rodriguez]

Thank you very much, I will try, I will comment on the results.

[Luis_Rodriguez]

Aleksi: The expression worked very well for me, but I get the highest value, but I need the value before the highest value

[Steve]

Try:

LOOKUP(
  MAXROW(
    “TableName”,
    “Date”,
    AND(
      ([_THISROW].[Machine Code] = [Machine Code]),
      ([_THISROW].[Date] > [Date])
    )
  ),
  “TableName”,
  “KeyColumn”,
  “Hour Meter”
)

[Luis_Rodriguez]

Thanks Steve, I attach an image of what I need, the column in yellow is data that I need to obtain, thanks for your support

[Aleksi]

@Luis_Rodriguez You are probably using your formula now in App formula. For me it sounds that you need that in initial value and then the Date evaluation is not needed. Though it depends where do you do the calculation.

[Luis_Rodriguez]

THIS IS THE TABLE I NEED TO GET ( the column in yellow is the query)

[Aleksi]

If you type this formula LOOKUP(MAXROW(“TableName”,“Date”,[Code]=[_THISROW].[Code]),“TableName”,“KeyColumn”,“Current Reading”) into your initial value, it will read the previous reading when new record is created. Please use your own table and key column name with the formula.

[Luis_Rodriguez]

Excellent, with this formula I am going to solve many problems I had, thanks Aleksi, I removed the blanks

[Steve · Accepted solution]

Remove the space between _ and THISROW. In fact, remove all spaces that aren’t within column or table names.

[Luis_Rodriguez]

Excellent, with this formula I will solve many problems I had, thanks Steve