Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.