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

 

 

 

Any help would be greatly aprecciated

 

Thanks.

Need Help

15 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    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-Forum/ba-p/963216

    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/1447523

  • Castillo3CS's avatar
    Castillo3CS
    Frequent Visitor
    1 year ago

    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
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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))
           )
       )

      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.

      • Castillo3CS's avatar
        Castillo3CS
        Frequent Visitor
        1 year ago

        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.