cancel
Showing results for
Did you mean:
Frequent Visitor

## tinmediff in same column when subgroups are repetative

Hi,
I'm trying to understand how much time a vehicle spent in each area. the problem is that the vehicle can go to the same area again, and I want to show each time he stepped in and out of an area.

for this example:

 plate area timestamp 111 1 2022-05-16T00:02:19 111 1 2022-05-16T00:32:39 111 2 2022-05-16T01:02:44 111 2 2022-05-16T01:02:46 111 1 2022-05-16T01:03:00 111 1 2022-05-16T01:20:00 111 1 2022-05-16T01:21:05 222 4 2022-05-16T01:22:00 222 4 2022-05-16T01:22:46 222 3 2022-05-16T02:00:46 222 3 2022-05-16T02:05:00 222 3 2022-05-16T02:07:00

the output required is:

 Plate Area Min Time Max Time Difference 111 1 2022-05-16T00:02:19 2022-05-16T00:32:39 00:30:20 111 2 2022-05-16T01:02:44 2022-05-16T01:02:46 00:00:02 111 1 2022-05-16T01:03:00 2022-05-16T01:21:05 00:18:05 222 4 2022-05-16T01:22:00 2022-05-16T01:22:46 00:00:46 222 3 2022-05-16T02:00:46 2022-05-16T02:07:00 00:06:14

2 ACCEPTED SOLUTIONS
Super User

Hi, @bar1694,

have a look at this report, it uses a couple of calculated columns to arrive at your desired result. If number of rows in your table is high(millions), you might want to consider doing this within Power Query, as calculated columns can be expensive.

The time difference is in seconds, I am leaving the transformation of seconds to hh:mm:dd to you.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

Super User

Hi @bar1694 ,

You should be able to do this using DAX. You can use EARLIER function to access the previous row. You can use the formulas below as a calculated column

``````Previous timestamp =
CALCULATE (
MAX ( 'Table'[timestamp] ),
ALLEXCEPT ( 'Table', 'Table'[plate] ),
'Table'[timestamp] < EARLIER ( 'Table'[timestamp] )
)``````
``````Time Difference =
IF (
NOT ( ISBLANK ( 'Table'[Previous timestamp] ) ),
'Table'[timestamp] - 'Table'[Previous timestamp]
)``````
``````Time Difference (hh:mm:ss) =
FORMAT ( 'Table'[Time Difference],"hh:mm:ss" )``````

Output:

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Connect to me on LinkedIn || Need consulting? Hire me for a Power BI gig on UpWork.
6 REPLIES 6
Super User

Hi @bar1694 ,

You should be able to do this using DAX. You can use EARLIER function to access the previous row. You can use the formulas below as a calculated column

``````Previous timestamp =
CALCULATE (
MAX ( 'Table'[timestamp] ),
ALLEXCEPT ( 'Table', 'Table'[plate] ),
'Table'[timestamp] < EARLIER ( 'Table'[timestamp] )
)``````
``````Time Difference =
IF (
NOT ( ISBLANK ( 'Table'[Previous timestamp] ) ),
'Table'[timestamp] - 'Table'[Previous timestamp]
)``````
``````Time Difference (hh:mm:ss) =
FORMAT ( 'Table'[Time Difference],"hh:mm:ss" )``````

Output:

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Connect to me on LinkedIn || Need consulting? Hire me for a Power BI gig on UpWork.
Frequent Visitor

Hi @danextian , thank you for your solution! i tried and it works, but now when I try to sum the time difference (hh:mm:ss) it won't let me because it's text. is there a way to sum the time difference? i didn't understand what units the number represents. and I want the sum is so it shows at the end as hh:mm:ss

thank you!

Frequent Visitor

thank you very much i'll try your solution!

Super User

Hi, @bar1694,

have a look at this report, it uses a couple of calculated columns to arrive at your desired result. If number of rows in your table is high(millions), you might want to consider doing this within Power Query, as calculated columns can be expensive.

The time difference is in seconds, I am leaving the transformation of seconds to hh:mm:dd to you.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

New Member

Hi @sturlaws ,

how can I use _prevArea variable in isStartOfGroup column, if the input in [Area] is string:

Area 1

Area 2, etc.

the min calculation is not working for that type of data

Frequent Visitor

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors