Forum Discussion
Travel Distance
- 7 years ago
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.
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.
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!