How much time did an user logged in?

[Anonymous]

Hello guys,

Supposed I have the following data of users, each time they logged in and logged out:

User    LoggingType   Date 

1         In                     2018-10-05 13:18:26.900

1         Out                  2018-10-05 13:18:54.353

1         In                     2018-10-05 13:19:23.637

1         Out                  2018-10-05 13:20:03.887

2         In                     2018-10-05 13:21:25.887

2         Out                  2018-10-05 13:22:01.247

3         In                     2018-10-10 18:24:33.867

3         Out                  2018-10-10 18:25:35.443

How do I create another column illustrating how long an user was logged in for a session? For example, here's what I wish to see:

User    LoggingType   Date                                      Duration

1         In                     2018-10-05 13:18:26.900      

1         Out                  2018-10-05 13:18:54.353      2018-10-05 13:18:54.353 - 2018-10-05 13:18:26.900

1         In                     2018-10-05 13:19:23.637

1         Out                  2018-10-05 13:20:03.887      2018-10-05 13:20:03.887 - 2018-10-05 13:19:23.637

2         In                     2018-10-05 13:21:25.887

2         Out                  2018-10-05 13:22:01.247      2018-10-05 13:22:01.247 - 2018-10-05 13:21:25.887

3         In                     2018-10-10 18:24:33.867

3         Out                  2018-10-10 18:25:35.443      2018-10-10 18:25:35.443 - 2018-10-10 18:24:33.867

[v-danhe-msft · Accepted solution]

Hi @Anonymous ,

Based on my test, you could refer to below steps:

Add index for your data in query editor:

Apply it and create below formula:

Column = var a=[Index]-1
return CALCULATE(MAX(Table1[Date]),FILTER('Table1','Table1'[Index]=a))

Result:

Now you could use the [Date] column to minus the [Column].

Regards,

Daniel He

[Anonymous]

Thanks this is what I was looking for

[GilbertQ]

Hi there

What you would need to do, is to create an Index column and then you can create a calculated column which will then put in the data on the corresponding row

This answer explains how to achieve this: https://community.powerbi.com/t5/Desktop/How-to-get-previous-row-value-for-text-column/td-p/312560