Number of miles since last report

[prj102]

I'm looking for a function that will allow me to calculate the number of miles a car has put on since the last time it was serviced.  Basically the function would filter on the "Car" column for the matching car and calculate the difference between the current "Total Miles" column and the most recent previous milage reported in the table.  

Example below, the "Miles since last report" is the column I would like to calculated automatically.

Car	Date	Total Miles	Miles since last report
Subaru Legacy	11/1/2018	17,353	0
Subaru Legacy	11/25/2018	17,851	498
Subaru Legacy	1/25/2019	19,515	1,664
Subaru Forester	11/1/2018	35,252	0
Subaru Forester	11/25/2018	36,363	1,111
Subaru Forester	1/25/2019	38,232	1,869
Subaru Forester	2/28/2019	39,968	1,736
Honda Civic	11/1/2018	93,526	0
Honda Civic	11/25/2018	93,888	362
Honda Civic	1/25/2019	95,025	1,137
Honda Civic	3/25/2019	98,252	3,227
Honda Civic	4/28/2019	100,025	1,773

[Ashish_Mathur]

Hi,

Would you want this to be a calculated column formula or a measure?

[parry2k · Accepted solution]

@prj102 you can add a column using following expression

# of Miles = 
VAR __prevMiles = CALCULATE( LASTNONBLANK( Table3[Total Miles],1 ), ALLEXCEPT(  Table3, Table3[Car]  ), Table3[Date] <EARLIER(Table3[Date])) 
RETURN IF(NOT  ISBLANK(  __prevMiles ), Table3[Total Miles] - __prevMiles )

[prj102]

This seems to work as I expected.  Thank you for the solution.  I'll reach back out if I find any issues.