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

Community Champion

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:

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
6 REPLIES 6
Community Champion

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:

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
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

Announcements

#### The Power BI Community Show

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

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!