Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Castillo3CS
Frequent Visitor

Calculating time between rows

‎10-04-2024 01:33 PM

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 idle (sensor_value < 701). 

 

I created a new colum: 

IdlleTime =

VAR next = MINX(FILTER(Ralentis,

                       Ralentis[bus_id]=EARLIER(Ralentis[bus_id]) &&

                       Ralentis[Date]>EARLIER(Ralentis[Date]) &&

                       Ralentis[sensor_value]> 701

               ),Ralentis[Date])

RETURN IF([sensor_value]>=701, 0,IF(ISBLANK(next),

               DATEDIFF([Date],NOW(),SECOND),

               DATEDIFF([Date],next,SECOND)

           )

       )

 

But I cant get it to work. Here's some sample data to give you an idea of what we're working with

 

Castillo3CS_1-1728073895710.png

 

 

Any help would be greatly aprecciated

 

Thanks.

Labels:
Message 1 of 16
679 Views
0
15 REPLIES 15
Castillo3CS
Frequent Visitor

‎10-07-2024 10:13 PM

Thanks for the promt reply. First time poster so I really appreciate the help.

 

Attached data and expected results. Hopefully I got it right this time.

 

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
14621Bus_002699.8759/25/20242:29:28 PM9/25/2024 2:29:28 PM1
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 PM1
14636Bus_002699.6259/25/20242:29:36 PM9/25/2024 2:29:36 PM1
14637Bus_002699.3759/25/20242:29:37 PM9/25/2024 2:29:37 PM1
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
14901Bus_003699.759/25/20242:31:58 PM9/25/2024 2:31:58 PM1
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 PM1
14904Bus_003700.59/25/20242:32:03 PM9/25/2024 2:32:03 PM4
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 PM1
14933Bus_003701.259/25/20242:32:14 PM9/25/2024 2:32:14 PM0
14934Bus_003699.59/25/20242:32:15 PM9/25/2024 2:32:15 PM1
14935Bus_003699.59/25/20242:32:15 PM9/25/2024 2:32:15 PM1
14936Bus_0037019/25/20242:32:15 PM9/25/2024 2:32:15 PM0
14937Bus_003699.8759/25/20242:32:16 PM9/25/2024 2:32:16 PM1
14938Bus_003700.59/25/20242:32:16 PM9/25/2024 2:32:16 PM1
14939Bus_003700.259/25/20242:32:16 PM9/25/2024 2:32:16 PM1
Message 3 of 16
592 Views
0
Anonymous
Not applicable
In response to Castillo3CS

‎10-08-2024 12:09 AM

Hi, @Castillo3CS 

 

I modified some of your formulas.

Next = MAXX(FILTER(Ralentis,
                       Ralentis[bus_id]=EARLIER(Ralentis[bus_id]) &&
                       Ralentis[Date]<EARLIER(Ralentis[Date])
               ),Ralentis[Date])
Column = IF([sensor_value]>=701, 0,IF(ISBLANK([Next]),
               ABS(DATEDIFF([Date],NOW(),SECOND)),
               ABS(DATEDIFF([Date],[Next],SECOND))
           )
       )

vzhangtinmsft_0-1728371299566.png

Please check, is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Calculating time between rows.pbix
Message 4 of 16
580 Views
0
Castillo3CS
Frequent Visitor
In response to Anonymous

‎10-08-2024 07:15 AM

Hi @Anonymous ,

 

I'm still getting some very odd values for time difference, notice the 1125431 and the 1125280. If we do the math between the two dates there’s now way we’re getting those numbers.

 

I was going to try something with index columns but I'm in the middle of implementing it, so I’m not sure it’ll work.

 

Thanks a lot for the help.

Message 5 of 16
572 Views
0
lbendlin
Super User
Super User
In response to Castillo3CS

‎10-08-2024 04:37 PM

Those are different buses.  Change the formula to return null instead.

Message 6 of 16
565 Views
0
Castillo3CS
Frequent Visitor
In response to lbendlin

‎10-08-2024 04:47 PM

My apologies, just noticed the mistake. When I sent the sample data I sorted it by Bus, the original data file comes with the IDs mixed, we have over 100 buses and data for them can come at the same time but recorded in different rows.

 

It looks something like this:

bus1 - time - value

bus1 - time - value

bus2 - time - vaue

bus1 - time - value

bus3 - time - value

.....

 

Hopefully this makes sense.

Message 7 of 16
563 Views
0
lbendlin
Super User
Super User
In response to Castillo3CS

‎10-08-2024 04:49 PM

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Message 8 of 16
558 Views
0
Castillo3CS
Frequent Visitor
In response to lbendlin

‎10-08-2024 04:57 PM

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
Message 9 of 16
553 Views
0
Ashish_Mathur
Super User
Super User
In response to Castillo3CS

‎10-08-2024 08:26 PM

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.

Ashish_Mathur_0-1728444375470.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 16 of 16
514 Views
ryan_mayu
Super User
Super User
In response to Castillo3CS

‎10-08-2024 05:53 PM

@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?

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Message 10 of 16
530 Views
0
Castillo3CS
Frequent Visitor
In response to ryan_mayu

‎10-08-2024 07:20 PM

Hi @ryan_mayu ,

 

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

Message 11 of 16
528 Views
0
ryan_mayu
Super User
Super User
In response to Castillo3CS

‎10-08-2024 08:14 PM

@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
 
11.PNG




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Message 12 of 16
520 Views
0
Castillo3CS
Frequent Visitor
In response to ryan_mayu

‎10-08-2024 09:35 PM

@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.

Message 13 of 16
490 Views
0
Castillo3CS
Frequent Visitor
In response to Castillo3CS

‎10-09-2024 08:24 AM

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

Message 14 of 16
470 Views
0
ryan_mayu
Super User
Super User
In response to Castillo3CS

‎10-09-2024 05:41 PM

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Message 15 of 16
459 Views
0
lbendlin
Super User
Super User

‎10-04-2024 03:40 PM

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Message 2 of 16
648 Views
0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

View All