Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DrHematite
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
OwenAuger
Super User
Super User

@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!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

@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!
Blog
Twitter
LinkedIn

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.