topic Re: Calculate Route Stop Based on Distance/Duration in DAX Commands and Tips

Calculate Route Stop Based on Distance/Duration

rsimpson318 — Mon, 02 May 2022 18:49:46 GMT

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 point was received. I was able to calculate the approximate distance between each point and the duration.

I would like to be able to determine when the vehicle is stationary and when it moves again. It could be stationary several times throughout the day and the duration can flucuate. Below is an example data set. Any help would be greatly appreciated.

Operator	Latitude	Longitude	Date/Time	TimeReceived	Distance	Duration
Driver 1	Lat1	Long1	4/29/2022 7:22:46 AM	4/29/2022 7:22:47 AM	0.00	0:00:20
Driver 2	Lat1	Long1	4/29/2022 7:23:06 AM	4/29/2022 7:23:07 AM	0.00	0:00:20
Driver 3	Lat1	Long1	4/29/2022 7:23:26 AM	4/29/2022 7:23:27 AM	0.00	0:00:20
Driver 4	Lat1	Long1	4/29/2022 7:23:46 AM	4/29/2022 7:23:47 AM	0.00	0:00:20
Driver 5	Lat1	Long1	4/29/2022 7:24:06 AM	4/29/2022 7:24:07 AM	0.00	0:00:20
Driver 6	Lat1	Long1	4/29/2022 7:24:26 AM	4/29/2022 7:24:27 AM	0.00	0:00:20
Driver 7	Lat1	Long1	4/29/2022 7:24:46 AM	4/29/2022 7:24:47 AM	0.00	0:00:20
Driver 8	Lat1	Long1	4/29/2022 7:25:06 AM	4/29/2022 7:25:07 AM	0.00	0:00:20
Driver 9	Lat1	Long1	4/29/2022 7:25:26 AM	4/29/2022 7:25:27 AM	0.00	0:00:20
Driver 10	Lat1	Long1	4/29/2022 7:25:46 AM	4/29/2022 7:25:47 AM	0.00	0:00:20
Driver 11	Lat2	Long2	4/29/2022 7:26:06 AM	4/29/2022 7:26:07 AM	0.01	0:00:21
Driver 12	Lat3	Long3	4/29/2022 7:26:27 AM	4/29/2022 7:26:27 AM	0.06	0:00:20
Driver 13	Lat4	Long4	4/29/2022 7:26:47 AM	4/29/2022 7:26:47 AM	0.05	0:00:19
Driver 14	Lat5	Long5	4/29/2022 7:27:06 AM	4/29/2022 7:27:07 AM	0.00	0:00:20
Driver 15	Lat6	Long6	4/29/2022 7:27:26 AM	4/29/2022 7:27:27 AM	0.00	0:00:21
Driver 16	Lat7	Long7	4/29/2022 7:27:47 AM	4/29/2022 7:27:47 AM	0.14	0:00:20
Driver 17	Lat8	Long8	4/29/2022 7:28:07 AM	4/29/2022 7:28:07 AM	0.18	0:00:20
Driver 18	Lat9	Long9	4/29/2022 7:28:27 AM	4/29/2022 7:28:27 AM	0.17	0:00:20
Driver 19	Lat10	Long10	4/29/2022 7:28:47 AM	4/29/2022 7:28:47 AM	0.02	0:00:20
Driver 20	Lat11	Long11	4/29/2022 7:29:07 AM	4/29/2022 7:29:07 AM	0.02	0:00:19
Driver 21	Lat12	Long12	4/29/2022 7:29:26 AM	4/29/2022 7:29:27 AM	0.00	0:00:20
Driver 22	Lat13	Long13	4/29/2022 7:29:46 AM	4/29/2022 7:29:47 AM	0.04	0:00:20
Driver 23	Lat14	Long14	4/29/2022 7:30:06 AM	4/29/2022 7:30:07 AM	0.35	0:00:20
Driver 24	Lat15	Long15	4/29/2022 7:30:26 AM	4/29/2022 7:30:27 AM	0.37	0:00:20
Driver 25	Lat16	Long16	4/29/2022 7:30:46 AM	4/29/2022 7:30:47 AM	0.21	0:00:21
Driver 26	Lat17	Long17	4/29/2022 7:31:07 AM	4/29/2022 7:31:07 AM	0.00	0:00:19
Driver 27	Lat18	Long18	4/29/2022 7:31:26 AM	4/29/2022 7:31:27 AM	0.00	0:00:20
Driver 28	Lat19	Long19	4/29/2022 7:31:46 AM	4/29/2022 7:31:47 AM	0.00	0:00:20
Driver 29	Lat20	Long20	4/29/2022 7:32:06 AM	4/29/2022 7:32:07 AM	0.01	0:00:21
Driver 30	Lat21	Long21	4/29/2022 7:32:27 AM	4/29/2022 7:32:27 AM	0.07	0:00:20
Driver 31	Lat22	Long22	4/29/2022 7:32:47 AM	4/29/2022 7:32:47 AM	0.14	0:00:20
Driver 32	Lat23	Long23	4/29/2022 7:33:07 AM	4/29/2022 7:33:07 AM	0.17	0:00:20
Driver 33	Lat24	Long24	4/29/2022 7:33:27 AM	4/29/2022 7:33:27 AM	0.22	0:00:20
Driver 34	Lat25	Long25	4/29/2022 7:33:47 AM	4/29/2022 7:33:47 AM	0.19	0:00:20
Driver 35	Lat26	Long26	4/29/2022 7:34:07 AM	4/29/2022 7:34:07 AM	0.15	0:00:20
Driver 36	Lat27	Long27	4/29/2022 7:34:27 AM	4/29/2022 7:34:27 AM	0.09	0:00:20
Driver 37	Lat28	Long28	4/29/2022 7:34:47 AM	4/29/2022 7:34:47 AM	0.01	0:00:20
Driver 38	Lat29	Long29	4/29/2022 7:35:07 AM	4/29/2022 7:35:07 AM	0.16	0:00:20
Driver 39	Lat30	Long30	4/29/2022 7:35:27 AM	4/29/2022 7:35:27 AM	0.08	0:00:20
Driver 40	Lat31	Long31	4/29/2022 7:35:47 AM	4/29/2022 7:35:47 AM	0.16	0:00:20
Driver 41	Lat32	Long32	4/29/2022 7:36:07 AM	4/29/2022 7:36:07 AM	0.17	0:00:19
Driver 42	Lat33	Long33	4/29/2022 7:36:26 AM	4/29/2022 7:36:27 AM	0.05	0:00:21
Driver 43	Lat34	Long34	4/29/2022 7:36:47 AM	4/29/2022 7:36:47 AM	0.01	0:00:20
Driver 44	Lat35	Long35	4/29/2022 7:37:07 AM	4/29/2022 7:37:07 AM	0.00	0:00:20
Driver 45	Lat35	Long35	4/29/2022 7:37:27 AM	4/29/2022 7:37:27 AM	0.00	0:00:19
Driver 46	Lat35	Long35	4/29/2022 7:37:46 AM	4/29/2022 7:37:47 AM	0.00	0:00:21
Driver 47	Lat35	Long35	4/29/2022 7:38:07 AM	4/29/2022 7:38:07 AM	0.00	0:00:20
Driver 48	Lat35	Long35	4/29/2022 7:38:27 AM	4/29/2022 7:38:27 AM	0.00	0:00:20
Driver 49	Lat35	Long35	4/29/2022 7:38:47 AM	4/29/2022 7:38:47 AM	0.00	0:00:19
Driver 50	Lat35	Long35	4/29/2022 7:39:06 AM	4/29/2022 7:39:07 AM	0.00	0:00:21
Driver 51	Lat35	Long35	4/29/2022 7:39:27 AM	4/29/2022 7:39:27 AM	0.00	0:00:20
Driver 52	Lat35	Long35	4/29/2022 7:39:47 AM	4/29/2022 7:39:47 AM	0.00	0:00:20
Driver 53	Lat35	Long35	4/29/2022 7:40:07 AM	4/29/2022 7:40:07 AM	0.00	0:00:20
Driver 54	Lat35	Long35	4/29/2022 7:40:27 AM	4/29/2022 7:40:27 AM	0.00	0:00:19
Driver 55	Lat35	Long35	4/29/2022 7:40:46 AM	4/29/2022 7:40:47 AM	0.00	0:00:20
Driver 56	Lat35	Long35	4/29/2022 7:41:06 AM	4/29/2022 7:41:07 AM	0.00	0:00:21
Driver 57	Lat35	Long35	4/29/2022 7:41:27 AM	4/29/2022 7:41:27 AM	0.00	0:00:20
Driver 58	Lat35	Long35	4/29/2022 7:41:47 AM	4/29/2022 7:41:47 AM	0.00	0:00:19
Driver 59	Lat35	Long35	4/29/2022 7:42:06 AM	4/29/2022 7:42:07 AM	0.00	0:00:21
Driver 60	Lat35	Long35	4/29/2022 7:42:27 AM	4/29/2022 7:42:27 AM	0.00	0:00:20
Driver 61	Lat35	Long35	4/29/2022 7:42:47 AM	4/29/2022 7:42:47 AM	0.00	0:00:19
Driver 62	Lat35	Long35	4/29/2022 7:43:06 AM	4/29/2022 7:43:07 AM	0.00	0:00:21
Driver 63	Lat35	Long35	4/29/2022 7:43:27 AM	4/29/2022 7:43:27 AM	0.00	0:00:20
Driver 64	Lat35	Long35	4/29/2022 7:43:47 AM	4/29/2022 7:43:47 AM	0.00	0:00:19
Driver 65	Lat35	Long35	4/29/2022 7:44:06 AM	4/29/2022 7:44:07 AM	0.00	0:00:21
Driver 66	Lat35	Long35	4/29/2022 7:44:27 AM	4/29/2022 7:44:27 AM	0.00	0:00:20
Driver 67	Lat35	Long35	4/29/2022 7:44:47 AM	4/29/2022 7:44:47 AM	0.00	0:00:20
Driver 68	Lat35	Long35	4/29/2022 7:45:07 AM	4/29/2022 7:45:07 AM	0.00	0:00:20
Driver 69	Lat35	Long35	4/29/2022 7:45:27 AM	4/29/2022 7:45:27 AM	0.00	0:00:20
Driver 70	Lat35	Long35	4/29/2022 7:45:47 AM	4/29/2022 7:45:47 AM	0.00	0:00:20
Driver 71	Lat35	Long35	4/29/2022 7:46:07 AM	4/29/2022 7:46:07 AM	0.00	0:00:20
Driver 72	Lat35	Long35	4/29/2022 7:46:27 AM	4/29/2022 7:46:27 AM	0.00	0:00:20
Driver 73	Lat35	Long35	4/29/2022 7:46:47 AM	4/29/2022 7:46:47 AM	0.00	0:00:19
Driver 74	Lat35	Long35	4/29/2022 7:47:06 AM	4/29/2022 7:47:07 AM	0.00	0:00:20

Re: Calculate Route Stop Based on Distance/Duration

tamerj1 — Tue, 03 May 2022 07:57:36 GMT

rsimpson318

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

Re: Calculate Route Stop Based on Distance/Duration

rsimpson318 — Tue, 03 May 2022 18:42:54 GMT

tamerj1

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.

Re: Calculate Route Stop Based on Distance/Duration

tamerj1 — Wed, 04 May 2022 10:15:33 GMT

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?

Re: Calculate Route Stop Based on Distance/Duration

tamerj1 — Wed, 04 May 2022 11:19:36 GMT

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.

Re: Calculate Route Stop Based on Distance/Duration

v-yanjiang-msft — Tue, 10 May 2022 01:44:37 GMT

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

Re: Calculate Route Stop Based on Distance/Duration

rsimpson318 — Wed, 18 May 2022 12:08:25 GMT

tamerj1

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.

Re: Calculate Route Stop Based on Distance/Duration

tamerj1 — Wed, 18 May 2022 13:13:08 GMT

rsimpson318
Here you go https://we.tl/t-71qnHty3mz

Re: Calculate Route Stop Based on Distance/Duration

rsimpson318 — Wed, 18 May 2022 19:35:05 GMT

tamerj1

Good afternoon. Thank you for your help with this. I had a chance to test the solution and it seems to still be a bit off.

It seems to calculate the Start and Stop time if the distance traveled is equal to zero. In my table, the distance can fluctuate ever so slightly. In these instances, it would need to consider the start/stop when the vehicle moves less than .05 miles over an extended period.
I also have multiple vehicles and multiple dates. So you might have one vehicle that has 3 stops on the first day, and 4 on the second, and then another vehicle that also has a few stops one day and so on.

Operator	Latitude	Longitude	Date/Time	TimeReceived	Distance	Duration	Stop
Driver 1	Lat1	Long1	4/29/2022 7:22	4/29/2022 7:22	0	0:00:20	Stop - 1
Driver 1	Lat1	Long1	4/29/2022 7:23	4/29/2022 7:23	0	0:00:20	Stop - 1
Driver 1	Lat1	Long1	4/29/2022 7:23	4/29/2022 7:23	0	0:00:20	Stop - 1
Driver 1	Lat1	Long1	4/29/2022 7:23	4/29/2022 7:23	0	0:00:20	Stop - 1
Driver 1	Lat1	Long1	4/29/2022 7:24	4/29/2022 7:24	0	0:00:20	Stop - 1
Driver 1	Lat1	Long1	4/29/2022 7:24	4/29/2022 7:24	0	0:00:20	Stop - 1
Driver 1	Lat1	Long1	4/29/2022 7:24	4/29/2022 7:24	0	0:00:20	Stop - 1
Driver 1	Lat1	Long1	4/29/2022 7:25	4/29/2022 7:25	0	0:00:20	Stop - 1
Driver 1	Lat1	Long1	4/29/2022 7:25	4/29/2022 7:25	0	0:00:20	Stop - 1
Driver 1	Lat1	Long1	4/29/2022 7:25	4/29/2022 7:25	0	0:00:20	Stop - 1
Driver 1	Lat2	Long2	4/29/2022 7:26	4/29/2022 7:26	0.01	0:00:21	Stop - 1
Driver 1	Lat3	Long3	4/29/2022 7:26	4/29/2022 7:26	0.06	0:00:20	Stop - 1
Driver 1	Lat4	Long4	4/29/2022 7:26	4/29/2022 7:26	0.05	0:00:19	Stop - 1
Driver 1	Lat5	Long5	4/29/2022 7:27	4/29/2022 7:27	0	0:00:20	Stop - 1
Driver 1	Lat6	Long6	4/29/2022 7:27	4/29/2022 7:27	0	0:00:21	Stop - 1
Driver 1	Lat7	Long7	4/29/2022 7:27	4/29/2022 7:27	0.14	0:00:20
Driver 1	Lat8	Long8	4/29/2022 7:28	4/29/2022 7:28	0.18	0:00:20
Driver 1	Lat9	Long9	4/29/2022 7:28	4/29/2022 7:28	0.17	0:00:20
Driver 1	Lat10	Long10	4/29/2022 7:28	4/29/2022 7:28	0.24	0:00:20
Driver 1	Lat11	Long11	4/29/2022 7:29	4/29/2022 7:29	0.11	0:00:19
Driver 1	Lat12	Long12	4/29/2022 7:29	4/29/2022 7:29	0.16	0:00:20
Driver 1	Lat13	Long13	4/29/2022 7:29	4/29/2022 7:29	0.04	0:00:20	Stop - 2
Driver 1	Lat14	Long14	4/29/2022 7:30	4/29/2022 7:30	0	0:00:20	Stop - 2
Driver 1	Lat15	Long15	4/29/2022 7:30	4/29/2022 7:30	0.03	0:00:20	Stop - 2
Driver 1	Lat16	Long16	4/29/2022 7:30	4/29/2022 7:30	0.01	0:00:21	Stop - 2
Driver 1	Lat17	Long17	4/29/2022 7:31	4/29/2022 7:31	0	0:00:19	Stop - 2
Driver 1	Lat18	Long18	4/29/2022 7:31	4/29/2022 7:31	0	0:00:20	Stop - 2
Driver 1	Lat19	Long19	4/29/2022 7:31	4/29/2022 7:31	0	0:00:20	Stop - 2
Driver 1	Lat20	Long20	4/29/2022 7:32	4/29/2022 7:32	0.01	0:00:21	Stop - 2
Driver 1	Lat21	Long21	4/29/2022 7:32	4/29/2022 7:32	0.5	0:00:20	Stop - 2
Driver 2	Lat30	Long30	4/29/2022 7:35	4/29/2022 7:35	0.08	0:00:20
Driver 2	Lat32	Long32	4/29/2022 7:36	4/29/2022 7:36	0.17	0:00:19
Driver 2	Lat33	Long33	4/29/2022 7:36	4/29/2022 7:36	0.05	0:00:21
Driver 2	Lat34	Long34	4/29/2022 7:36	4/29/2022 7:36	0.01	0:00:20	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:37	4/29/2022 7:37	0	0:00:20	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:37	4/29/2022 7:37	0	0:00:19	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:37	4/29/2022 7:37	0	0:00:21	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:38	4/29/2022 7:38	0	0:00:20	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:38	4/29/2022 7:38	0	0:00:20	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:38	4/29/2022 7:38	0	0:00:19	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:39	4/29/2022 7:39	0	0:00:21	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:39	4/29/2022 7:39	0	0:00:20	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:39	4/29/2022 7:39	0	0:00:20	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:40	4/29/2022 7:40	0	0:00:20	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:40	4/29/2022 7:40	0	0:00:19	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:40	4/29/2022 7:40	0	0:00:20	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:41	4/29/2022 7:41	0	0:00:21	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:41	4/29/2022 7:41	0	0:00:20	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:41	4/29/2022 7:41	0	0:00:19	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:42	4/29/2022 7:42	0	0:00:21	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:42	4/29/2022 7:42	0	0:00:20	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:42	4/29/2022 7:42	0	0:00:19	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:43	4/29/2022 7:43	0	0:00:21	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:43	4/29/2022 7:43	0	0:00:20	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:43	4/29/2022 7:43	0	0:00:19	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:44	4/29/2022 7:44	0	0:00:21	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:44	4/29/2022 7:44	0	0:00:20	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:44	4/29/2022 7:44	0	0:00:20	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:45	4/29/2022 7:45	0	0:00:20	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:45	4/29/2022 7:45	0	0:00:20	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:45	4/29/2022 7:45	0	0:00:20	Stop - 1
Driver 2	Lat35	Long35	4/29/2022 7:46	4/29/2022 7:46	0.16	0:00:20

Re: Calculate Route Stop Based on Distance/Duration

tamerj1 — Fri, 20 May 2022 04:54:51 GMT

rsimpson318
Actually while writing the code, I was trying to figure out the criteria but that was not clear so I assumed and waited for your feedback on the proposed solution.

When you say "it would need to consider the start/stop when the vehicle moves less than .05 miles over an extended period." please define "extended period."

Re: Calculate Route Stop Based on Distance/Duration

tamerj1 — Mon, 23 May 2022 09:28:32 GMT

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"

Re: Calculate Route Stop Based on Distance/Duration

rsimpson318 — Mon, 23 May 2022 11:38:36 GMT

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.

Re: Calculate Route Stop Based on Distance/Duration

rsimpson318 — Mon, 23 May 2022 19:23:34 GMT

Good afternoon tamerj1 .

When I try to create the column for Adjusted Location, it gives an error that I've ran out of memory. Did you run into this when you were working on it?

Re: Calculate Route Stop Based on Distance/Duration

tamerj1 — Tue, 24 May 2022 01:32:28 GMT

Morning rsimpson318

on sample I had no error. But this a heavy calculation and this is expected for big data. Do you have table for each vehicle or all in one table?

Re: Calculate Route Stop Based on Distance/Duration

rsimpson318 — Tue, 24 May 2022 11:49:17 GMT

tamerj1 When I ran it, I had approximately 37,000 rows of data. I trimmed that down to one vehicle for one day which was about 1,100 and it did run. It still didn't look like it was calculating correctly but I hadn't had a chance to look into it further.

Once complete, I plan to have all the data in a single table, capturing a month at a time. I expect to have around 600,000 rows.