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...
Forum Discussion
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
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.