logout time

[rajasekar_o]

Hi team ,

i have Login logout data, need to calculate  OUT TIME 

out time
if the employee have logout time on that date the take max of logout time ,if ther is no logout time on that date the check nexday before 9:30:00am take that time is logout time other wise is empty 

  i try but not come correctly 
21.11.2023 out time is 02:30:02 am

[Anonymous · Accepted solution]

Hi @rajasekar_o ,

Please follow these steps:

1.Use the DAX expression shown to create a calculated column named ‘ In Time ’.

In time = TIME(HOUR([Datetime]),MINUTE([Datetime]),SECOND([Datetime]))

2. Use the DAX expression shown to create a measure named ‘ Out time ’.

Out time =
VAR cur_name =
    SELECTEDVALUE ( 'Table'[Name] )
VAR cur_dt =
    SELECTEDVALUE ( 'Table'[Datetime] )
VAR cur_date =
    DATE ( YEAR ( cur_dt ), MONTH ( cur_dt ), DAY ( cur_dt ) )
VAR next_date = cur_date + 1
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        DATE ( YEAR ( [Datetime] ), MONTH ( [Datetime] ), DAY ( [Datetime] ) ) = cur_date
            && [Type] = "OUT"
    )
VAR max_date =
    MAXX ( tmp, [Datetime] )
VAR max_time =
    TIME ( HOUR ( max_date ), MINUTE ( max_date ), SECOND ( max_date ) )
VAR tmp2 =
    FILTER (
        ALL ( 'Table' ),
        [Name] = cur_name
            && DATE ( YEAR ( [Datetime] ), MONTH ( [Datetime] ), DAY ( [Datetime] ) ) = next_date
            && [Type] = "OUT"
    )
VAR tmp3 =
    FILTER ( tmp2, [In time] < TIMEVALUE ( "9.30.00 AM" ) )
VAR max_time1 =
    MAXX ( tmp3, [In time] )
RETURN
    IF ( ISBLANK ( COUNTROWS ( tmp ) ), max_time1, max_time )

3. Use the DAX expression shown to create a measure for the filter.

Measure =
VAR cur_name =
    SELECTEDVALUE ( 'Table'[Name] )
VAR cur_dt =
    SELECTEDVALUE ( 'Table'[Datetime] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [Name] = cur_name && [Type] = "IN" )
VAR tmp1 =
    ADDCOLUMNS (
        tmp,
        "Date", DATE ( YEAR ( [Datetime] ), MONTH ( [Datetime] ), DAY ( [Datetime] ) )
    )
VAR tmp2 =
    SUMMARIZE ( tmp1, [Date], "Min_Date", MIN ( [Datetime] ) )
VAR tmp3 =
    SELECTCOLUMNS ( tmp2, "Min_date", [Min_Date] )
RETURN
    IF ( cur_dt IN tmp3, 1 )

4. Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.