cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Travel Distance

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Super User I

## Re: Travel Distance

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.

Owen Auger

2 REPLIES 2
Highlighted Super User I

## Re: Travel Distance

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.

Owen Auger

Highlighted
Frequent Visitor

## Re: Travel Distance

@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.

Announcements #### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge! #### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event. Top Solution Authors
Top Kudoed Authors
Users online (873)