tinmediff in same column when subgroups are repetative

[bar1694]

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

[sturlaws · Accepted solution]

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.

[danextian · Accepted solution]

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:

Sample PBIX: https://drive.google.com/file/d/1doQzTCRNQfaw3JQzz1J3ST5luVURtC14/view?usp=sharing 

[bar1694]

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! 

[bar1694]

thank you very much i'll try your solution!

[sturlaws · Accepted solution]

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.

[agamnitz]

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

[bar1694]

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