cancel
Showing results for
Search instead 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

please help me! 🙂

2 ACCEPTED SOLUTIONS
Resident Rockstar

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:

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

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
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:

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

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
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!

Resident Rockstar

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

thank you for your help  i'll try your solution!

## Helpful resources

Announcements

#### Check it Out!

Click here to learn more about the August 2022 updates!

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors