Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
supriya_13
Helper I
Helper I

I need to calculate total time between two dates based on login and logout Date.

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.

 

AgentDescription Time
Agent1Login01/07/2021 9:50
Agent1Not Ready01/07/2021 9:50
Agent1Ready01/07/2021 9:51
Agent3Login01/07/2021 10:56
Agent3Not Ready01/07/2021 10:56
Agent3Ready01/07/2021 11:03
Agent3Not Ready01/07/2021 12:04
Agent3Ready01/07/2021 12:04
Agent3Not Ready01/07/2021 12:25
Agent3Ready01/07/2021 12:42
Agent1Not Ready01/07/2021 12:46
Agent1Ready01/07/2021 13:00
Agent3Not Ready01/07/2021 13:27
Agent1Not Ready01/07/2021 13:48
Agent3Ready01/07/2021 13:48
Agent1Ready01/07/2021 13:54
Agent3Not Ready01/07/2021 15:18
Agent3Ready01/07/2021 15:30
Agent1Logout01/07/2021 16:00
Agent3Not Ready01/07/2021 17:13
Agent3Ready01/07/2021 17:14
Agent3Not Ready01/07/2021 17:59
Agent3Ready01/07/2021 18:09
Agent3Not Ready01/07/2021 18:54
Agent3Ready01/07/2021 18:58
Agent3Not Ready01/07/2021 19:22
Agent3Ready01/07/2021 19:24
Agent3Logout01/07/2021 20:01
Agent3Login02/07/2021 13:02
Agent3Not Ready02/07/2021 13:02
Agent3Ready02/07/2021 13:03
Agent3Not Ready02/07/2021 13:03
Agent3Ready02/07/2021 13:13
Agent3Not Ready02/07/2021 13:13
Agent3Ready02/07/2021 13:26
Agent3Not Ready02/07/2021 17:28
Agent3Ready02/07/2021 17:44
Agent3Logout02/07/2021 19:00
Agent1Login03/07/2021 14:00
Agent1Not Ready03/07/2021 14:00
Agent1Ready03/07/2021 14:00
Agent1Not Ready03/07/2021 15:33
Agent1Ready03/07/2021 15:36
Agent1Not Ready03/07/2021 16:47
Agent1Ready03/07/2021 16:58
Agent1Logout03/07/2021 20:00

 

 



2 ACCEPTED SOLUTIONS
mahoneypat
Employee
Employee

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

 

Date = DATE(YEAR(Logins[Time]), Month(Logins[Time]), DAY(Logins[Time]))

 

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.

 

mahoneypat_0-1627906819368.png

 

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 ( withlogoutsDATEDIFF ( Logins[Time], [cNextLogout], MINUTE ) )

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

v-kkf-msft
Community Support
Community Support

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

View solution in original post

5 REPLIES 5
v-kkf-msft
Community Support
Community Support

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

mahoneypat
Employee
Employee

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

 

Date = DATE(YEAR(Logins[Time]), Month(Logins[Time]), DAY(Logins[Time]))

 

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.

 

mahoneypat_0-1627906819368.png

 

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 ( withlogoutsDATEDIFF ( Logins[Time], [cNextLogout], MINUTE ) )

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI 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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI 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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.