How to get miles between with multiple filters?

[Jason_1981]

Hello, 

Been trying at this for a week now and keep getting wrong results or else a calculation that results in a time-out memory allocation issue. 

What I have is data on tire changes. The key fields are [Unit], [Asy Cd], [Position], [Date Out], & [Odo]. What I want to do is for each tire change event, go back and find the last date the same unit had a hit for same Asy Cd & Position, then calculate the difference between the previous miles [Odo] to the current row miles. 

Link to Google sheet sample data:

https://docs.google.com/spreadsheets/d/1q8-cR0tmfaBkiiWGpN7Um3vGay0r1J1A/edit?usp=sharing&ouid=11116...

Thank You 

[johnt75]

You could add a calculated column like

Miles difference =
VAR CurrentMiles = 'Table'[Odo]
VAR CurrentDate = 'Table'[Date out]
VAR PrevMiles =
    SELECTCOLUMNS (
        CALCULATETABLE (
            TOPN ( 1, 'Table', 'Table'[Date out] ),
            ALLEXCEPT ( 'Table', 'Table'[Unit], 'Table'[Asy Cd], 'Table'[Position] ),
            'Table'[Date out] < CurrentDate
        ),
        "@val", 'Table'[Odo]
    )
RETURN
    CurrentMiles - PrevMiles

[Jason_1981]

@johnt75  Thanks for the response. When I enter this I get the error message "A table of multiple values was supplied where a single value was expected."

[johnt75]

There must be multiple entries for the same unit on the same day. If the table has a unique column then you could add that to the TOPN as an additional sort after the date. If you don't have one, you could sort by date in Power Query and then add an index column and use that.