Calculating Durations between Sign In/Out events in a user log

[azywoniki]

Hi all,

I am working with a user log of sign in and sign out events indicating the date/time the user either signed in or out with a status of 1 for "SignIn" and 0 for "SignOut".

From this table I would like to calculate a duration for the amount of time each user was signed in.  Essentially, this would be the time between each sequential "SignIn" event and "SignOut" event.  If the final user event is a "SignIn" event, I would calculate the duration up to the current date/time.

I would like to accomplish this in Power BI but struggling on how to shape the data appropriately or to be able to create a measure to capture this.

Any help would be appreciated!

Below is a sample data table:

EventDateTime	UserName	UserStatus
8/1/2019 3:09	Betty	1
8/1/2019 7:40	Gary	1
8/1/2019 10:39	Sue	1
8/1/2019 11:12	Gary	0
8/1/2019 13:00	Gary	1
8/1/2019 13:09	Betty	0
8/1/2019 13:34	Betty	1
8/1/2019 15:05	Betty	0
8/1/2019 16:48	Betty	1
8/1/2019 16:57	John	0
8/1/2019 18:55	John	1
8/1/2019 19:12	John	0
8/1/2019 19:59	Betty	0
8/1/2019 20:38	Betty	1
8/1/2019 20:45	John	1
8/1/2019 21:41	Gary	0
8/1/2019 22:14	Sue	1
8/1/2019 22:56	Gary	1
8/1/2019 22:59	Gary	0
8/1/2019 23:00	Betty	0
8/1/2019 23:33	Sue	0
8/2/2019 1:10	John	0
8/2/2019 1:24	John	1
8/2/2019 1:48	Betty	1
8/2/2019 3:03	Gary	1
8/2/2019 10:36	Sue	1
8/2/2019 11:29	Sue	0
8/2/2019 12:13	Betty	0
8/2/2019 13:09	John	0

[v-eachen-msft · Accepted solution]

Hi  @azywoniki ,

At first, you need to create an index to sort data by name by group.

index =
RANKX (
    FILTER ( Table1, EARLIER ( Table1[UserName] ) = Table1[UserName] ),
    Table1[EventDateTime],
    ,
    ASC
)

Then put sign-in time and sign-out time on the same row.

OutTime =
CALCULATE (
    IF (
        ISBLANK ( FIRSTNONBLANK ( 'Table1'[EventDateTime], 1 ) ),
        NOW (),
        FIRSTNONBLANK ( 'Table1'[EventDateTime], 1 )
    ),
    FILTER (
        'Table1',
        Table1[index]
            = EARLIER ( Table1[index] ) + 1
            && Table1[UserName] = EARLIER ( Table1[UserName] )
    )
)

Create a new table to remove meaningless data.

Table2 =
CALCULATETABLE ( Table1, FILTER ( Table1, Table1[UserStatus] = 1 ) )

Create a measure to calculate duration by minute.

Duration(Minute) =
CALCULATE (
    DATEDIFF (
        SELECTEDVALUE ( 'Table2'[EventDateTime] ),
        SELECTEDVALUE ( 'Table2'[OutTime] ),
        MINUTE
    )
)

Here is the document about DATEDIFF() function. You can change interval according to your own needs.

https://docs.microsoft.com/en-us/dax/datediff-function-dax

At last, you can get your visual.

Best Regards,

Eads

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

[azywoniki]

Eads,

Thank you very much for the thorough explanation of a workable solution.  I was able to use your suggestion to do exactly what I needed.  Apologies for the delayed response, it to me some time to break free and fully understand your solution.

Once again thanks for the assistance.

Cheers