Calculating distance between rows

[icarecenter]

I record odometer readings through out the day.  I want to calculate the difference between each entry.  How do I get the data from the previous row?

[Markus_Malessa]

Assuming you have a table called OdometerReadings and columns for [Difference], [Odometer], [ID] as key column, and [Timestamp] and possibly also a 'REF' type column for a vehicle if you are doing this for multiple vehicles [VehicleID]?

Then your formula for [Difference] should be:

[Odometer] - ANY(SELECT(OdometerReadings[Odometer], [ID] = MAXROW("OdometerReadings", "Timestamp", AND([Timestamp] < [_THISROW].[Timestamp], [VehicleID] = [_THISROW].[VehicleID]))))

Just a friendly reminder to include more information in your question next time so we don't have to guess what your table structure is.

[icarecenter]

Table 1 

columns [Difference], [Odometer], [Row ID], [Date]

modified formula

[Odometer] - ANY(SELECT(Table 1[Odometer], [Row ID] = MAXROW("Table 1", "Date", ([Date] < [_THISROW].[Date]))))

The result is the current odometer reading.  It's subtracting 0.

[Difference]=8357-8251

Also, how do I account for the first one for the day?  It shouldn't subtract from the previous row if it's a different day.

[icarecenter]

Update:  It looks like it subtracts from the original odometer.  I want to subtract from the row above it with the same date.

[Markus_Malessa]

Note that my assumed column is [Timestamp], i.e. DateTime, not just Date.

---

@icarecenter wrote:

I want to subtract from the row above it with the same date.

---

Also, I don't think this statement is entirely accurate because how would this work for the first entry of a day when there is no other entry with the "same date"?

Now that I see what your data structure actually is, I would probably suggest adding two virtual columns [StartTimestamp] and [EndTimestamp] 'DateTime' type and let the formulas be as follows:

[Date] + [Start Time]
and
[Date] + [End Time]

Then set [Difference] as follows:

[Odometer] - ANY(SELECT(Table 1[Odometer], [Row ID] = MAXROW("Table 1", "EndTimestamp", ([EndTimestamp] < [_THISROW].[StartTimestamp]))))

I am also assuming that you probably have an action to start a trip log and then end a trip log, so in your case you might actually benefit from doing a start odometer and end odometer instead of figuring out the difference from the previous entry. But then you would probably need to figure out 'unaccounted mileage' in a similar fashion in case someone forgets to log a trip.

[Markus_Malessa]

---

@icarecenter wrote:

I want to subtract from the row above it with the same date.

---

Also, I don't think this statement is entirely accurate because how would this work for the first entry of a day when there is no other entry with the "same date"?

Now that I see what your data structure actually is, I would probably suggest adding two virtual columns [StartTimestamp] and [EndTimestamp] 'DateTime' type and let the formulas be as follows:

[Date] + [Start Time]
and
[Date] + [End Time]

Then set [Difference] as follows:

[Odometer] - ANY(SELECT(Table 1[Odometer], [Row ID] = MAXROW("Table 1", "EndTimestamp", ([EndTimestamp] < [_THISROW].[StartTimestamp]))))

I am also assuming that you probably have an action to start a trip log and then end a trip log, so in your case you might actually benefit from doing a start odometer and end odometer instead of figuring out the difference from the previous entry. But then you would probably need to figure out 'unaccounted mileage' in a similar fashion in case someone forgets to log a trip.

[icarecenter]

Thank you.  I think you're right.  I will put in a start odometer and end odometer.  Can you help me with the formula to retrieve the previous [end odometer] from the previous row?  I can use that as an initial value for the [start odometer] for the next row.  I think this probably would be simpler.

[Markus_Malessa · Accepted solution]

You may try this, however no guarantees it will work without doing a virtual column for start times and end times as a combined DateTime type column:

ANY(SELECT(Table 1[EndOdometer], [Row ID] = MAXROW("Table 1", ([Date] + [End Time]), (([Date] + [End Time]) < ([_THISROW].[Date] + [_THISROW].[Start Time])))))