cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Super User I

Re: Travel Distance

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


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

2 REPLIES 2
Highlighted
Super User I
Super User I

Re: Travel Distance

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


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

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.

Appreciate your help!

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

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