Forum Discussion
Calculate Route Stop Based on Distance/Duration
- 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.
It would either be another table or even a card that would show something like:
| Stop | Start Time | End Time | Duration |
| 1st Stop | 4/29/2022 7:22:46 AM | 4/29/2022 7:26:07 AM | 3min20sec |
| 2nd Stop | 4/29/2022 7:37:07 AM | 4/29/2022 7:47:07 AM | 10min0sec |
The formatting doesn't need to be exact. Let me know if this helps or you need more info.
Thank you.
Hi rsimpson318
apologies for the late response.
we're having holidays that shall end today. I will reply to you max by tomorrow morning.
however, I Need a confirmation on which date column to conside? The Date/Time column or the ToimeReceived Column?