Date difference between dates in different rows

[miguelsus2000]

Hi,

I've seen some examples out in the internet, but cannot seem to solve it yet.  I have the following table

      Timestamp                User     Location

1      1/22/2022 3:41:00    UserA   Inside

2      1/22/2022 3:50:00    UserB   Inside

3      1/22/2022 3:50:00    UserA   Outside

4      1/22/2022 3:51:00    UserA   Inside

5      1/22/2022 4:00:00    UserA   Inside

Longest consecutive (no skipping) duration between Inside to Inside for every user and longest consecutive duration for every user for Outside to Outside. 

Example:

    UserA - there are 3 readings (1,4,5), but longest duration is 1->4 (10mins) since 4->5 is only 9mins. So result is 10mins.

I would also like to create a table for each user (Inside column and Outside column) with all the durations stored in case i need to manipulate the data like search for min or count ranges.

Thank you.

M

[v-henryk-mstf]

Hi @miguelsus2000 ,

Try the formula like below:

col2 = 
VAR rank_ =
    RANKX (
        FILTER ( ALL ( Test ), Test[Location] = EARLIER ( Test[Location] ) ),
        Test[ID]
    )
VAR _min =
    CALCULATE (
        MAX ( Test[Timestamp] ),
        ALLEXCEPT ( Test, Test[User], Test[Location] ),
        Test[Timestamp] < EARLIER ( Test[Timestamp] )
    )
RETURN
    IF ( rank_=1, blank(), DATEDIFF ( _min, [Timestamp], minute))

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[miguelsus2000]

Thank you... i tried another one that seemed to work for me.  Thank you.

[amitchandak · Accepted solution]

@miguelsus2000 , A new column like this can help

new column =
var _min = maxx(filter(Table, [user] =earlier([user]) && [Location] = earlier([Location]) && [Timestamp] < earlier([Timestamp])), [Timestamp])
return
datediff(_min, [Timestamp], second)

[miguelsus2000]

Thank you amitchandak, this one worked for me.