Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Everyone ,
I need to calculate total time between two dates based on login and logout Date and time for each agents.
so the filters are like :
1) Total time between from login to logout
2) for each day
3) for every Agent.
Agent | Description | Time |
Agent1 | Login | 01/07/2021 9:50 |
Agent1 | Not Ready | 01/07/2021 9:50 |
Agent1 | Ready | 01/07/2021 9:51 |
Agent3 | Login | 01/07/2021 10:56 |
Agent3 | Not Ready | 01/07/2021 10:56 |
Agent3 | Ready | 01/07/2021 11:03 |
Agent3 | Not Ready | 01/07/2021 12:04 |
Agent3 | Ready | 01/07/2021 12:04 |
Agent3 | Not Ready | 01/07/2021 12:25 |
Agent3 | Ready | 01/07/2021 12:42 |
Agent1 | Not Ready | 01/07/2021 12:46 |
Agent1 | Ready | 01/07/2021 13:00 |
Agent3 | Not Ready | 01/07/2021 13:27 |
Agent1 | Not Ready | 01/07/2021 13:48 |
Agent3 | Ready | 01/07/2021 13:48 |
Agent1 | Ready | 01/07/2021 13:54 |
Agent3 | Not Ready | 01/07/2021 15:18 |
Agent3 | Ready | 01/07/2021 15:30 |
Agent1 | Logout | 01/07/2021 16:00 |
Agent3 | Not Ready | 01/07/2021 17:13 |
Agent3 | Ready | 01/07/2021 17:14 |
Agent3 | Not Ready | 01/07/2021 17:59 |
Agent3 | Ready | 01/07/2021 18:09 |
Agent3 | Not Ready | 01/07/2021 18:54 |
Agent3 | Ready | 01/07/2021 18:58 |
Agent3 | Not Ready | 01/07/2021 19:22 |
Agent3 | Ready | 01/07/2021 19:24 |
Agent3 | Logout | 01/07/2021 20:01 |
Agent3 | Login | 02/07/2021 13:02 |
Agent3 | Not Ready | 02/07/2021 13:02 |
Agent3 | Ready | 02/07/2021 13:03 |
Agent3 | Not Ready | 02/07/2021 13:03 |
Agent3 | Ready | 02/07/2021 13:13 |
Agent3 | Not Ready | 02/07/2021 13:13 |
Agent3 | Ready | 02/07/2021 13:26 |
Agent3 | Not Ready | 02/07/2021 17:28 |
Agent3 | Ready | 02/07/2021 17:44 |
Agent3 | Logout | 02/07/2021 19:00 |
Agent1 | Login | 03/07/2021 14:00 |
Agent1 | Not Ready | 03/07/2021 14:00 |
Agent1 | Ready | 03/07/2021 14:00 |
Agent1 | Not Ready | 03/07/2021 15:33 |
Agent1 | Ready | 03/07/2021 15:36 |
Agent1 | Not Ready | 03/07/2021 16:47 |
Agent1 | Ready | 03/07/2021 16:58 |
Agent1 | Logout | 03/07/2021 20:00 |
Solved! Go to Solution.
Here is a measure expression that shows one way to do it. Note that I first added a calculated column with just the date so I could use it in the measure with
You could also do that in the query editor even easier. Below is the result putting the measure in a table visual with the Agent column. It would also work if you added the new date column, but it will likely not work if you add the login time to this visual.
Here is the measure
Avg Daily Login Duration =
VAR dailylogins =
GENERATE (
SUMMARIZE ( Logins, Logins[Agent], Logins[Date] ),
CALCULATETABLE ( DISTINCT ( Logins[Time] ), Logins[Description ] = "Login" )
)
VAR withlogouts =
ADDCOLUMNS (
dailylogins,
"cNextLogout",
VAR logintime = Logins[Time]
RETURN
CALCULATE (
MIN ( Logins[Time] ),
ALL ( Logins[Time] ),
Logins[Time] > logintime,
Logins[Description ] = "Logout"
)
)
RETURN
AVERAGEX ( withlogouts, DATEDIFF ( Logins[Time], [cNextLogout], MINUTE ) )
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @supriya_13 ,
Has your problem been solved? If it is solved, please mark a reply which is helpful to you.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
Hi @supriya_13 ,
Has your problem been solved? If it is solved, please mark a reply which is helpful to you.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
Here is a measure expression that shows one way to do it. Note that I first added a calculated column with just the date so I could use it in the measure with
You could also do that in the query editor even easier. Below is the result putting the measure in a table visual with the Agent column. It would also work if you added the new date column, but it will likely not work if you add the login time to this visual.
Here is the measure
Avg Daily Login Duration =
VAR dailylogins =
GENERATE (
SUMMARIZE ( Logins, Logins[Agent], Logins[Date] ),
CALCULATETABLE ( DISTINCT ( Logins[Time] ), Logins[Description ] = "Login" )
)
VAR withlogouts =
ADDCOLUMNS (
dailylogins,
"cNextLogout",
VAR logintime = Logins[Time]
RETURN
CALCULATE (
MIN ( Logins[Time] ),
ALL ( Logins[Time] ),
Logins[Time] > logintime,
Logins[Description ] = "Logout"
)
)
RETURN
AVERAGEX ( withlogouts, DATEDIFF ( Logins[Time], [cNextLogout], MINUTE ) )
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
The previous version works with your sample data, but didn't address multiple logins per day. This one should, and should give the correct "total" too.
Avg Daily Login Duration =
VAR namesdates =
SUMMARIZE ( Logins, Logins[Agent], Logins[Date] )
VAR dailylogins =
GENERATE (
namesdates,
CALCULATETABLE ( DISTINCT ( Logins[Time] ), Logins[Description ] = "Login" )
)
VAR withlogouts =
ADDCOLUMNS (
dailylogins,
"cNextLogout",
VAR logintime = Logins[Time]
RETURN
CALCULATE (
MIN ( Logins[Time] ),
ALL ( Logins[Time] ),
Logins[Time] > logintime,
Logins[Description ] = "Logout"
)
)
VAR result =
AVERAGEX (
namesdates,
VAR thisagent = Logins[Agent]
VAR thisdate = Logins[Date]
RETURN
DIVIDE (
SUMX (
FILTER ( withlogouts, Logins[Agent] = thisagent && Logins[Date] = thisdate ),
DATEDIFF ( Logins[Time], [cNextLogout], MINUTE )
),
60
)
)
RETURN
result
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please provide your data in a copy/paste format (add a table to your post with the data) instead of an image. Or provide a link to an Excel, csv, or even better a pbix file on OneDrive, Google Drive, etc. Also, clarify if an Agent might log in and out more than once in a day, and explain how you will use the measure. Will it be used in a table visual for example with Agent name and date?
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat I have added the data in table .
Yes, agent can login or logout from system multiple times in a day . I need to calculate the total time in a day agent was logged in.
Ready or not Ready is active or inactive , so that doesn't matter untill they are in system.
For Visual , I will be having a summary table that gives the details like login time , logout time and total time in each day.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |