Forum Discussion

Castillo3CS's avatar
Castillo3CS
Frequent Visitor
1 year ago

Calculating time between rows

Hi Team,   We're having trouble calculating time elapsed for vehicles that are idling. Sensor data for each vehicle is being sent and we need to know how much time a vehicle (bus_id) has spent in i...
Need Help
Castillo3CS's avatar
Castillo3CS
Frequent Visitor
1 year ago

Here's the sampel data arranged to look like ou table.

 

IDbus_idsensor_valuedate1time1DateExpected
14618Bus_002699.759/25/20242:29:27 PM9/25/2024 2:29:27 PM0
14619Bus_002700.259/25/20242:29:28 PM9/25/2024 2:29:28 PM1
14620Bus_0027019/25/20242:29:28 PM9/25/2024 2:29:28 PM0
14901Bus_003699.759/25/20242:31:58 PM9/25/2024 2:31:58 PM0
14902Bus_003699.8759/25/20242:31:59 PM9/25/2024 2:31:59 PM1
14903Bus_003700.1259/25/20242:31:59 PM9/25/2024 2:31:59 PM0
14904Bus_003700.59/25/20242:32:03 PM9/25/2024 2:32:03 PM4
14621Bus_002699.8759/25/20242:29:28 PM9/25/2024 2:29:28 PM0
14622Bus_002699.259/25/20242:29:32 PM9/25/2024 2:29:32 PM4
14634Bus_002700.1259/25/20242:29:36 PM9/25/2024 2:29:36 PM4
14635Bus_002699.8759/25/20242:29:36 PM9/25/2024 2:29:36 PM0
14931Bus_0037009/25/20242:32:13 PM9/25/2024 2:32:13 PM10
14932Bus_003699.6259/25/20242:32:13 PM9/25/2024 2:32:13 PM0
14636Bus_002699.6259/25/20242:29:36 PM9/25/2024 2:29:36 PM0
14637Bus_002699.3759/25/20242:29:37 PM9/25/2024 2:29:37 PM1
14936Bus_0037019/25/20242:32:15 PM9/25/2024 2:32:14 PM2
14937Bus_003699.8759/25/20242:32:16 PM9/25/2024 2:32:15 PM1
14938Bus_003700.59/25/20242:32:16 PM9/25/2024 2:32:15 PM0
14638Bus_002700.759/25/20242:29:37 PM9/25/2024 2:29:37 PM1
14639Bus_002700.3759/25/20242:29:37 PM9/25/2024 2:29:37 PM1
14900Bus_002700.259/25/20242:31:58 PM9/25/2024 2:31:58 PM141
14933Bus_003701.259/25/20242:32:14 PM9/25/2024 2:32:15 PM0
14934Bus_003699.59/25/20242:32:15 PM9/25/2024 2:32:16 PM1
14935Bus_003699.59/25/20242:32:15 PM9/25/2024 2:32:16 PM0
14939Bus_003700.259/25/20242:32:16 PM9/25/2024 2:32:16 PM0
Ashish_Mathur's avatar
Ashish_Mathur
Super User
1 year ago

Hi,

I used these calculated column formulas

Date and time = Data[date1]+Data[time1]
Diff = if(ISBLANK(CALCULATE(MAX(Data[Date and time]),FILTER(Data,Data[bus_id]=EARLIER(Data[bus_id])&&Data[Date and time]<EARLIER(Data[Date and time])))),time(0,0,0),Data[Date and time]-CALCULATE(MAX(Data[Date and time]),FILTER(Data,Data[bus_id]=EARLIER(Data[bus_id])&&Data[Date and time]<EARLIER(Data[Date and time]))))

Hope this helps.