Accurate application usage duration from a transaction table

[Anonymous]

Hello,

I do have in Power BI the follwoing transactional data where it shows when a user was logging on and off in an application:

What I need is to create a report where I show the following:

1. How many hours:minutes:seconds a specific user was using the application till now e.g., User CHTSI is using the app for 9 seconds
2. How many hours:minutes:seconds in total the application has been used till now irrespective of the user using it. e.g., the appllication has been used today for 2hours 3 minutes and 12 seconds

It might be the case that a user XYZ apeears in the transaction log with an on record but there is no off record yet because the user is still using the application.

[Greg_Deckler]

Have a look at this article on Mean Time Between Failure (MTBF). Essentially the same type of calculation and you will want to use EARLIER.  See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[Anonymous]

Thanks @Greg_Deckler indeed you have a great article there. However I am not that lucky to have both timestamps in the same column. As you can see I do have the on/off events on different lines. Furthermore, I do have entries that look like the one below, since two sessions can run in parallel.

Activity Timestamp User

on          13:53:52    XYZ

on          13:55:30    XYZ

off          15:16:40    XYZ

off          15:16:43    XYZ

[v-jiascu-msft]

HI @Anonymous,

1. How to get the result 9 seconds of user CHTSI? It's 45 seconds in my opinion.

2. How to check which two are a pair when the sessions run in parallel?

Best Regards,

Dale

[Anonymous]

Hi @v-jiascu-msft (Dale),

1. the value of 9 seconds was an example, not a precise calculation. Sorry.

2. You don't know which two are a pair but you don't care because you need to calculate the total time an app was used. So any two sequetial pairs you select if you don't select the same row twice will give you the same sum. See an example below.

Transactions of user XYZ starting and stoping the app

ID Timestamp(sec) Type

1   t=1                  start

2   t=2                  start

3   t=4                 stop

4   t=6                  start

5   t=8                 stop

6   t=9                 stop

Lets say that somehow you know that the real pairs were (ID1, ID6) (ID2, ID3) and (ID4, ID5) so te total time that app was used by user XYZ would be (9-1)+(4-2)+(8-6)=(8+2+2)=12 sec

However during your calculation you selected start and stops sequentaly without knowing what really happened (ID1, ID3) (ID2, ID5) and (ID4, ID6) so the total time that the app was used by user XYZ would be claclulated to be (4-1)+(8-2)+(9-6)= 3+6+3=12 sec. The challenge in the last step is to not select (ID4, ID5) as the last pair since ID5 has been selected before in pair (ID2, ID5).

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

We can pivot the table and the times will pair automatically. You can check out the demo in the attachment.

1. Sort the column User first, then the column Timestamp;

2. Add an index;

3. Add a custom column;

if [Activity] = "on" then [Index] else null

4. Right click the Custom column, choose fill down;

5. Remove the Index column;

6. Click the column Activity and then click the Timestamp with "Ctrl", pivot these two columns;

7. Remove the column Custom.

8. Apply the changes;

9. Add a calculated column;

Duration =
IF (
    ISBLANK ( Table2[on] ),
    9999999999,
    IF (
        ISBLANK ( Table2[off] ),
        DATEDIFF ( TIMEVALUE ( [on] ), TIMEVALUE ( NOW () ), SECOND ),
        DATEDIFF ( [on], [off], SECOND )
    )
)

10. Create a table visual, filter the [off] is blank, you can get the answer of your first question.

Best Regards,

Dale

[Anonymous]

Awesome and neat solution! Thanks a lot Dale.