Need help to Calculate Time duration

[Anonymous]

Hello All,

I have swipe card details table as below

Here i would like to calculate the time duration from In and out each time.

Here in above image we need to get the time duration from 1st I01 to 1st O01 like wise for every In and Out.

But at last I01 theres no Out, so here from that In time to current time calculate the time duration.

How can i calculate the time duration.

Any suggestion please

Mohan V

[Zubair_Muhammad]

Hi @Anonymous

Try this column

=
VAR OutTime =
    CALCULATE (
        MIN ( TableName[Time] ),
        FILTER (
            TableName,
            TableName[Time] > EARLIER ( TableName[Time] )
                && TableName[In/Out] = "O01"
        )
    )
VAR Endtime =
    IF ( ISBLANK ( OutTime ), NOW (), OutTime )
RETURN
    IF ( TableName[In/Out] = "I01", DATEDIFF ( TableName[Time], EndTime, MINUTE ) )

[Anonymous]

Hi @Zubair_Muhammad 

I have tried what you have suggested.

but it is not giving me the right output

Can you please help me to get this done..

It will help me a lot.

Thank you.

Mohan V

[Zubair_Muhammad · Accepted solution]

@Anonymous

Try this

=
VAR OutTime =
    CALCULATE (
        MIN ( TableName[Time] ),
        FILTER (
            TableName,
            TableName[Time] > EARLIER ( TableName[Time] )
                && TableName[Employee Name] = EARLIER ( TableName[Employee Name] )
                && TableName[In/Out] = "O01"
        )
    )
VAR Endtime =
    IF ( ISBLANK ( OutTime ), NOW (), OutTime )
RETURN
    IF ( TableName[In/Out] = "I01", DATEDIFF ( TableName[Time], EndTime, MINUTE ) )