Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

To calculate difference between In time and Out time for bio-metric report

  Hi ,   I am designing a reports on biometric timimngs,but i am facing difficulties to find break timing. I am attaching a snapshot for more clarifiaction.   11:48:11 is shift timing and...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous,

     

    Please new such a measure:

    Diff =
VAR lastOUTTime =
    CALCULATE (
        MAX ( TableA[Out Time] ),
        FILTER (
            ALLEXCEPT ( TableA, TableA[Employee Code], TableA[Date] ),
            TableA[RNK]
                = CALCULATE (
                    MAX ( TableA[RNK] ),
                    FILTER (
                        ALLEXCEPT ( TableA, TableA[Employee Code], TableA[Date] ),
                        TableA[RNK] < MAX ( TableA[RNK] )
                            && TableA[Direction] = "OUT"
                    )
                )
        )
    )
RETURN
    DATEDIFF (
        IF ( SELECTEDVALUE ( TableA[Direction] ) = "IN", lastOUTTime, BLANK () ),
        SELECTEDVALUE ( TableA[In Time] ),
        SECOND
    )

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
7 years ago

Hi Anonymous,

 

Please new such a measure:

Diff =
VAR lastOUTTime =
    CALCULATE (
        MAX ( TableA[Out Time] ),
        FILTER (
            ALLEXCEPT ( TableA, TableA[Employee Code], TableA[Date] ),
            TableA[RNK]
                = CALCULATE (
                    MAX ( TableA[RNK] ),
                    FILTER (
                        ALLEXCEPT ( TableA, TableA[Employee Code], TableA[Date] ),
                        TableA[RNK] < MAX ( TableA[RNK] )
                            && TableA[Direction] = "OUT"
                    )
                )
        )
    )
RETURN
    DATEDIFF (
        IF ( SELECTEDVALUE ( TableA[Direction] ) = "IN", lastOUTTime, BLANK () ),
        SELECTEDVALUE ( TableA[In Time] ),
        SECOND
    )

 

Best regards,

Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hey v-yulgu-msft 

 

Thank You.

 

Your formula worked but when i take multile userid's then it shows like : 

 

 

Thanks & Regards,

Vivek Chavan