cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bar1694
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:

plateareatimestamp
11112022-05-16T00:02:19
11112022-05-16T00:32:39
11122022-05-16T01:02:44
11122022-05-16T01:02:46
11112022-05-16T01:03:00
11112022-05-16T01:20:00
11112022-05-16T01:21:05
22242022-05-16T01:22:00
22242022-05-16T01:22:46
22232022-05-16T02:00:46
22232022-05-16T02:05:00
22232022-05-16T02:07:00

 

the output required is:

PlateAreaMin TimeMax TimeDifference
11112022-05-16T00:02:192022-05-16T00:32:3900:30:20
11122022-05-16T01:02:442022-05-16T01:02:4600:00:02
11112022-05-16T01:03:002022-05-16T01:21:0500:18:05
22242022-05-16T01:22:002022-05-16T01:22:4600:00:46
22232022-05-16T02:00:462022-05-16T02:07:0000:06:14

 

please help me! 🙂

2 ACCEPTED SOLUTIONS
sturlaws
Super User
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.

View solution in original post

danextian
Super User
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:

danextian_0-1653435982677.png

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

 






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

View solution in original post

6 REPLIES 6
danextian
Super User
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:

danextian_0-1653435982677.png

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

 






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

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! 

thank you very much i'll try your solution!

sturlaws
Super User
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.

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
Frequent Visitor

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors