Calculate Route Stop Based on Distance/Duration

rsimpson318 

Would you please provide sample of the expected results? Thank you

Hi rsimpson318 ,

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.
Best Regards,
Community Support Team _ kalyj

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"