Date Diff based on Multiple Daily Sessions

[knmath01]

Hello!

I have looked around for the last couple days on here and tried several different calculated columns and measure's but I can't quite get to what I am looking for. I want to calculate the session time per person in a day. A person can have multiple sessions per day and currently there is an id per log in/log it but not per what I would consider a session (log in to log out or log in to expired session). Below is a sample of my data and what I think would be what I would want to then create a date diff off of:

Sample Date

USER	DATE	STATUS	LOGIN ID
Bob Smith	3/10/2022 8:28:45 AM	SUCCESS	1239260
Bob Smith	3/10/2022 9:15:29 AM	SESSION EXPIRED	1239350
Bob Smith	3/10/2022 9:36:25 AM	SUCCESS	1239386
Bob Smith	3/10/2022 9:49:23 AM	SUCCESS	1239411
Bob Smith	3/10/2022 10:07:31AM	SESSION EXPIRED	1239453
Kathy Smith	3/10/2022 3:25:39 PM	SUCCESS	1240197
Kathy Smith	3/10/2022 4:04:53 PM	SESSION EXPIRED	1240290
Kathy Smith	3/10/2022 4:13:46 PM	SUCCESS	1240313
Kathy Smith	3/10/2022 4:43:55 PM	SESSION EXPIRED	1240353

What I think would work best is:

SessionID	User	Sucess Log	Session Expire
1	Bob Smith	3/10/2022 8:28:45 AM	3/10/2022 9:15:29 AM
2	Bob Smith	3/10/2022 9:36:25 AM	BLANK
3	Bob Smith	3/10/2022 9:49:23 AM	3/10/2022 10:07:31AM
4	Kathy Smith	3/10/2022 3:25:39 PM	3/10/2022 4:04:53 PM
5	Kathy Smith	3/10/2022 4:13:46 PM	3/10/2022 4:43:55 PM

And I would think off that I could then do something to get how long each session was in hours and minutes? Anything were there is no session expire we essentially ignore when we look at the report now. 

I appreciate any advice or help, especially if I am over thinking this. 

Thank you!!

[amitchandak]

@knmath01 , Create a new column

new column =
var _max = minx(filter(Table, [User] = earlier([User]) && [DATE] > earlier([Date])), [Date])
var _status = minx(filter(Table, [User] = earlier([User]) && [DATE] =_max), [status])
return
if(_status = "SESSION EXPIRED", _max, blank())

nor you can ignore SESSION EXPIRED rows

[knmath01]

@amitchandak Hi, thank you for the quick response! I created a column using the above and it didn't produce any results, the whole column is blank. Below is what I did for the column:

SessionTime =

var _max = minx(filter(UserSessions, [USER] = earlier([USER]) && [DATE] > earlier([DATE])), [DATE])

var _status = minx(filter(UserSessions, [USER] = earlier([USER]) && [DATE].[Date] =_max), [STATUS])

return

if(_status = "SESSION EXPIRED", _max, blank())