Forum Discussion
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! 🙂
Hi, Anonymous,
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.Hi Anonymous ,
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 columnPrevious 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
6 Replies
- sturlaws
Resident Rockstar
Hi, Anonymous,
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.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
- danextian
Super User
Hi Anonymous ,
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 columnPrevious 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
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!