Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Find duration from login and logout in same column

Hello, i having problem to find duration from login and logout for same User ID. however i find a solution in Calculating time between check in and check out from different rows  and it give me t...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Not sure why you have the LOOKUPVALUE step.  Please try this expression instead:

     

    Duration =
VAR __thislogin = 'Employee-event'[Time]
VAR __nextlogout =
    CALCULATE (
        MIN ( 'Employee-event'[Time] ),
        ALLEXCEPT ( 'Employee-event', 'Employee-event'[UserID] ),
        'Employee-event'[Team] > __thislogin,
        'Employee-event'[Event] = "OUT"
    )
RETURN
    IF (
        'Employee-event'[Event] = "IN",
        DATEDIFF ( __thislogin, __nextlogout, MINUTE ),
        BLANK ()
    )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

     

     

  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    Please check again. I just replicated the formula which mahoneypat  has given and it gives the correct answers.

     

    Pls check again.

     

     

     

     

    Column =
    VAR __thislogin = 'Table'[Time]
    VAR __nextlogout =
    CALCULATE (
    MIN ( 'Table'[Time] ),
    ALLEXCEPT ( 'Table', 'Table'[User ID] ),
    'Table'[Time] > __thislogin,
    'Table'[Event] = "OUT"
    )
    RETURN
    IF (
    'Table'[Event] = "IN",
    DATEDIFF ( __thislogin, __nextlogout, MINUTE ),
    BLANK ()
    )

     

     

     

     

     

    Regards,

    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

Not sure why you have the LOOKUPVALUE step.  Please try this expression instead:

 

Duration =
VAR __thislogin = 'Employee-event'[Time]
VAR __nextlogout =
    CALCULATE (
        MIN ( 'Employee-event'[Time] ),
        ALLEXCEPT ( 'Employee-event', 'Employee-event'[UserID] ),
        'Employee-event'[Team] > __thislogin,
        'Employee-event'[Event] = "OUT"
    )
RETURN
    IF (
        'Employee-event'[Event] = "IN",
        DATEDIFF ( __thislogin, __nextlogout, MINUTE ),
        BLANK ()
    )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Unfortunately, this did not work. These are the results

 

 

Some records are correct while others are wrong

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    Please check again. I just replicated the formula which mahoneypat  has given and it gives the correct answers.

     

    Pls check again.

     

     

     

     

    Column =
    VAR __thislogin = 'Table'[Time]
    VAR __nextlogout =
    CALCULATE (
    MIN ( 'Table'[Time] ),
    ALLEXCEPT ( 'Table', 'Table'[User ID] ),
    'Table'[Time] > __thislogin,
    'Table'[Event] = "OUT"
    )
    RETURN
    IF (
    'Table'[Event] = "IN",
    DATEDIFF ( __thislogin, __nextlogout, MINUTE ),
    BLANK ()
    )

     

     

     

     

     

    Regards,

    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

    • Rajla3's avatar
      Rajla3
      Regular Visitor
      6 years ago

      Could you please help me ??

      I have the same problem , but I want to turn the duration from log in and log out intu hh:mm:ss.

       

      Also I have some users which are log in but they aren't log out that day..Could you please help me how to make a case in order to close the time for that user, I mean to count only the hours in that certain date.