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.
Would you please provide sample of the expected results? Thank you
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.
- tamerj14 years ago
Community Champion
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.- rsimpson3184 years agoFrequent Visitor
Good morning and sorry for the late response. I was out on leave and didn't have a chance to download the file and the link seems to have expired. Would you mind reposting it?
Thank you.
- tamerj14 years ago
Community Champion
rsimpson318
Here you go https://we.tl/t-71qnHty3mz
- tamerj14 years ago
Community Champion
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?