Travel Distance

[DrHematite]

I have some data in the following format:

Machine ID | Date            | Latitude          | Longitude
1                 | 12/02/2019 | -29.37584       | 130.38429
2                 | 12/02/2019 | -29.64453       | 131.49304
1                 | 13/02/2019 | -29.37610       | 130.38419
2                 | 13/02/2019 | -29.65132       | 131.41320

And I need to be able to measure the distance each machine (by machine ID) has travelled on a given day (An example, in the row for Machine ID 1, the distance on 12/02 would be the difference between its position on 12/02 and 13/02.)
I know the formula for the displacement:

Δ = acos(sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(lon2-lon1))*R 

Where lat1, lat2, lon1 and lon2 are the respective latitudes and longitudes, and R is the radius of the Earth.
Problem is, I can't figure out how to refer to the next record by date, for a given machine ID. 
Any suggestions?

[OwenAuger · Accepted solution]

@DrHematite 

Here's an example of DAX code that could be used in a calculated column to find the next date for the current machine, and the corresponding Lat & Long, which can then be used to compute the distance (I've called the table Machines😞

Distance =
VAR CurrentDate = Machines[Date]
VAR NextDate =
    CALCULATETABLE (
        FIRSTNONBLANK ( Machines[Date], 0 ),
        ALLEXCEPT ( Machines, Machines[Machine ID] ),
        Machines[Date] > CurrentDate
    )
VAR Lat2 =
    CALCULATE (
        SELECTEDVALUE ( Machines[Latitude] ),
        ALLEXCEPT ( Machines, Machines[Machine ID] ),
        NextDate
    )
VAR Long2 =
    CALCULATE (
        SELECTEDVALUE ( Machines[Longitude] ),
        ALLEXCEPT ( Machines, Machines[Machine ID] ),
        NextDate
    )
...

You could also do something similar in Power Query if you preferred.

[DrHematite]

@OwenAuger 

I think there's a problem somewhere in my formula (as I'm getting nonsense answers), but I've used your example to return the Lat2 and Long2 to new columns for testing purposes in the meantime, and that seems to have worked correctly.

Appreciate your help!