Forum Discussion
Accurate application usage duration from a transaction table
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:
- 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
- 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.
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
6 Replies
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/339586
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
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
