Forum Discussion

rsimpson318's avatar
rsimpson318
Frequent Visitor
4 years ago
Solved

Calculate Route Stop Based on Distance/Duration

Hello,   I'm trying to figure out a DAX or calculated column to determine a route stop based on the duration and distance traveled (or not). I have a table with a list of points and the time each p...
  • tamerj1's avatar
    tamerj1
    4 years ago

    rsimpson318 
    Hello agian
    I had some free time to do work on it. Here is a sample file folr your reference https://we.tl/t-WjszOERm4s
    My proposed solution involves adding calculated columns:

    Start Time = 
VAR CurrentDistance =
    Routes[Distance]
VAR LocationTable =
    CALCULATETABLE ( Routes, ALLEXCEPT ( Routes, Routes[Latitude], Routes[Longitude] ) )
RETURN
IF ( 
    CurrentDistance = 0 && COUNTROWS ( LocationTable ) > 1,
    MINX ( 
        LocationTable,
        Routes[Date/Time] 
    )
)
    End Time = 
VAR CurrentDistance =
    Routes[Distance]
VAR LocationTable =
    CALCULATETABLE ( Routes, ALLEXCEPT ( Routes, Routes[Latitude], Routes[Longitude] ) )
RETURN
IF ( 
    CurrentDistance = 0 && COUNTROWS ( LocationTable ) > 1,
    MAXX ( 
        LocationTable,
        Routes[Date/Time] 
    )
)
    Stop = 
VAR StopNumber =
    RANKX ( FILTER ( Routes, NOT ISBLANK ( Routes[Start Time] ) ), Routes[Start Time],, ASC, Dense )
RETURN
    IF ( 
        NOT ISBLANK ( Routes[Start Time] ),
        "Stop - " & StopNumber
    )

    Then the measure would be

    Stop Duration = 
VAR Duration =  
    DATEDIFF ( MAX ( Routes[Start Time] ), MAX ( Routes[End Time] ), SECOND )
RETURN
    QUOTIENT ( Duration, 60 ) & "Min and " & MOD ( Duration, 60 ) & " Sec"

    One last step would be to filter the table in order to remove the blank row

    The report looks like this

    I hope this satisfies your requirement.

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi rsimpson318 
You did not reply to my question. Not sure if you are still interested in the solution. Anyway, please refer to the sample file with the updated solution https://we.tl/t-so64yQQdvX

I started with grouping latitude and longitude in one column for the ease of calculation

 

Location = Routes[Latitude] & " : " & Routes[Longitude]

 

As the location keeps changing despite the vehicle is considered not moving, then the location need to be updated to accommodate with our our moving/stopping criteria 

 

Adjusted Location = 
IF ( 
    Routes[Distance] <= 0.06,
    VAR PreviousTable = FILTER ( Routes, Routes[Date/Time] < EARLIER ( Routes[Date/Time] ) )
    VAR PreviousStopTable = FILTER ( PreviousTable, Routes[Distance] <= 0.06 )
    VAR PreviousMoveTable = FILTER ( PreviousTable, Routes[Distance] > 0.06 )
    VAR EarliestStopTime = MINX ( PreviousStopTable, Routes[Date/Time] )
    VAR EarliestStopLocation = MINX ( FILTER ( PreviousStopTable, Routes[Date/Time] = EarliestStopTime ), Routes[Location] )
    VAR EarlierStopTime = MAXX ( PreviousStopTable, Routes[Date/Time] )
    VAR EarlierStopLocation = MAXX ( FILTER ( PreviousStopTable, Routes[Date/Time] = EarlierStopTime ), Routes[Location] )
    VAR EarlierMoveTime = MAXX ( PreviousMoveTable, Routes[Date/Time] )
    VAR CurrentStopTime = MAXX ( FILTER ( PreviousMoveTable, Routes[Date/Time] < EarlierMoveTime ), Routes[Date/Time] )
    VAR CurrentStopLocation = MAXX ( FILTER ( PreviousMoveTable, Routes[Date/Time] = CurrentStopTime ), Routes[Location] )
    RETURN
        COALESCE ( IF ( ISBLANK ( EarlierMoveTime ), EarliestStopLocation, CurrentStopLocation ), Routes[Location] )
)

 

Start and end times for each stop are calculated

 

Start Time = 
VAR CurrentDistance =
    Routes[Distance]
VAR LocationTable =
    CALCULATETABLE ( Routes, ALLEXCEPT ( Routes, Routes[Adjusted Location] ) )
RETURN
IF ( 
    CurrentDistance <= 0.06 && COUNTROWS ( LocationTable ) > 1,
    MINX ( 
        LocationTable,
        Routes[Date/Time] 
    )
)
End Time = 
VAR CurrentDistance =
    Routes[Distance]
VAR LocationTable =
    CALCULATETABLE ( Routes, ALLEXCEPT ( Routes, Routes[Adjusted Location] ) )
RETURN
IF ( 
    CurrentDistance <= 0.06 && COUNTROWS ( LocationTable ) > 1,
    MAXX ( 
        LocationTable,
        Routes[Date/Time] 
    )
)

 

Then the stops 

 

Stop = 
VAR StopNumber =
    RANKX ( FILTER ( Routes, NOT ISBLANK ( Routes[Start Time] ) ), Routes[Start Time],, ASC, Dense )
RETURN
    IF ( 
        NOT ISBLANK ( Routes[Start Time] ),
        "Stop - " & StopNumber
    )

 

 Finally the measur 

Stop Duration = 
VAR Duration =  
    DATEDIFF ( MAX ( Routes[Start Time] ), MAX ( Routes[End Time] ), SECOND )
RETURN
    QUOTIENT ( Duration, 60 ) & "Min and " & MOD ( Duration, 60 ) & " Sec"

rsimpson318's avatar
rsimpson318
Frequent Visitor
4 years ago

Good morning tamerj1.

 

I apologize for the late responses. I am still interested in a solution and I'll try this out today and see how the data looks with it.

 

To answer your question regarding "extended period": I wouldn't need to capture small periods where they aren't moving like instances where they were stopped at a traffic light or quickly getting gas. I would say any instance where they have haven't moved more than .05 miles in more than 5 minutes would need to be considered an actual stop.