Find duration from login and logout in same column

[AliAlHamadi]

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 this resulte 

2

by using this DAX

Col1 =
CALCULATE (
MIN ( 'Employee-event'[Index] ),
FILTER (
ALLEXCEPT ( 'Employee-event', 'Employee-event'[UserID] ),
'Employee-event'[Index] > EARLIER ( 'Employee-event'[Index] )
&& 'Employee-event'[Event] = "OUT"
)
)

TIME OUT =
LOOKUPVALUE (
'Employee-event'[Time],
'Employee-event'[Index], 'Employee-event'[Col1]
)

Duration in Min =
IF (
'Employee-event'[Event] = "IN",
DATEDIFF ( 'Employee-event'[Time], 'Employee-event'[TIME OUT], MINUTE ),
BLANK ()
)

it's very good but i was unable to apply to huge data(over million rows) run out of memory 

There is anyway to do it in Query Editor or other solution

[mahoneypat · Accepted solution]

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

[mahoneypat · Accepted solution]

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

[seajaytx]

This worked perfect for me to analysis employee badging data. Thanks!

[AliAlHamadi]

Unfortunately, this did not work. These are the results

Some records are correct while others are wrong

[harshnathani · Accepted solution]

Hi @AliAlHamadi ,

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]

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.