Forum Discussion

DrHematite's avatar
DrHematite
Frequent Visitor
7 years ago
Solved

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...
  • OwenAuger's avatar
    7 years ago

    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.