Calculating time between rows

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.

Castillo3CS 

 Let's only take a look at bus 2

| ID    | bus_id  | sensor_value | date1     | time1      | Date                 | Expected |
| ----- | ------- | ------------ | --------- | ---------- | -------------------- | -------- |
| 14618 | Bus_002 | 699.75       | 9/25/2024 | 2:29:27 PM | 9/25/2024 2:29:27 PM | 0        |
| 14619 | Bus_002 | 700.25       | 9/25/2024 | 2:29:28 PM | 9/25/2024 2:29:28 PM | 1        |
| 14620 | Bus_002 | 701          | 9/25/2024 | 2:29:28 PM | 9/25/2024 2:29:28 PM | 0        |
| 14621 | Bus_002 | 699.875      | 9/25/2024 | 2:29:28 PM | 9/25/2024 2:29:28 PM | 0        |
| 14622 | Bus_002 | 699.25       | 9/25/2024 | 2:29:32 PM | 9/25/2024 2:29:32 PM | 4        |
| 14634 | Bus_002 | 700.125      | 9/25/2024 | 2:29:36 PM | 9/25/2024 2:29:36 PM | 4        |
| 14635 | Bus_002 | 699.875      | 9/25/2024 | 2:29:36 PM | 9/25/2024 2:29:36 PM | 0        |
| 14636 | Bus_002 | 699.625      | 9/25/2024 | 2:29:36 PM | 9/25/2024 2:29:36 PM | 0        |
| 14637 | Bus_002 | 699.375      | 9/25/2024 | 2:29:37 PM | 9/25/2024 2:29:37 PM | 1        |
| 14638 | Bus_002 | 700.75       | 9/25/2024 | 2:29:37 PM | 9/25/2024 2:29:37 PM | 1        |
| 14639 | Bus_002 | 700.375      | 9/25/2024 | 2:29:37 PM | 9/25/2024 2:29:37 PM | 1        |
| 14900 | Bus_002 | 700.25       | 9/25/2024 | 2:31:58 PM | 9/25/2024 2:31:58 PM | 141      |

could you pls explain how we get the expected output? why the last one is 141?

Hi ryan_mayu ,

Sure, 141 is the difference in seconds between 2:29:37 and 2:31:58.

Castillo3CS 

you can try this

Column =

VAR _last=maxx(FILTER('Table','Table'[ID]<EARLIER('Table'[ID])&&'Table'[bus_id]=EARLIER('Table'[bus_id])),'Table'[ID])

VAR _time=maxx(FILTER('Table','Table'[ID]=_last),'Table'[Date])

return if(ISBLANK(_time),0,DATEDIFF(_time,'Table'[Date],SECOND))

 

However, the output is slightly different from your expected.

 

for bus 3, your expected output is 2, but what I get is -2

 

ryan_mayu 

Error was me copying the values to the sample table. I'm testing your solution but PowerBi is taking a very long time to create the new column. We have over 1.4 million rows so I'm not surprised. Will let you know once it’s done.

Hi ryan_mayu , I had to terminate the process after 2 hours of "working on it", any ideas?

I don't have better solution for this. Maybe you can wait for longer time or let's see if anyone else have better solution for this.