Forum Discussion
Date difference between dates in different rows
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
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)
4 Replies
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)Thank you amitchandak, this one worked for me.
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.Thank you... i tried another one that seemed to work for me. Thank you.
