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 I have to calculate difference between  13:44:35 and 13:44:08  ,14:41:50 and 14:15:51 ...so on

is there any way to calculate time difference using dax ?

 

Thank You in advance

 

 

Regards,

Vivek Chavan 

 

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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

     

    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 I have to calculate difference between  13:44:35 and 13:44:08  ,14:41:50 and 14:15:51 ...so on

    is there any way to calculate time difference using dax ?

     

    Thank You in advance

     

     

    Regards,

    Vivek Chavan 

     

  • LivioLanzo's avatar
    LivioLanzo
    Icon for Solution Sage rankSolution Sage
    7 years ago

    Anonymous

    could you share your table? and what do you do want to do after having had calculated this value? where should it be displayed?

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft 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 

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hey guys,

         

        Find many difficulties to show break times data for multiple employees, so i am trying to show total minutes worked and total break time but i am not able to calculate total break time.

         

        I am attaching a sql query and expected output for clarification :

         

        Query :

         

        SELECT USERID,EMPLOYEENAME,[DATE],MIN([TIME]) [IN TIME] ,
        MAX([TIME]) [OUT TIME],DATEDIFF(MINUTE,MIN([TIME]),MAX([TIME])) [TOTAL HOURS WORKED],DEPARTMENT-- into Timedetails
        FROM
        (
        select
        [ROW NUMBER]=(row_number() over(partition by a.USERID order by a.logdate)),
        A.USERID,B.EMPLOYEENAME,
        CAST(A.LogDate AS DATE) [DATE],
        CONVERT(VARCHAR,A.LOGDATE,108) [TIME],
        DIRECTION=(CASE WHEN VerificationMode='1' THEN 'IN' ELSE 'OUT' END),
        A.VERIFICATIONMODE-- INTO Timedetails
        ,c.DepartmentFName DEPARTMENT
        from DeviceLogs_10_2018 a
        inner join
        Employees b
        on(a.UserId=B.EmployeeCode)
        inner join
        Departments c
        on (b.DepartmentId=c.DepartmentId)
        where A.UserId IN ('2298','2299') and cast(A.LogDate as date)='2018-10-17'
        GROUP BY B.EMPLOYEENAME,A.LogDate,A.VerificationMode,A.USERID,c.DepartmentFName
        )A
        GROUP BY USERID,EMPLOYEENAME,[DATE],DEPARTMENT
        order by [DATE] desc

         

        Expected O/P:

         

         

         

        Thank You !!

         

        Regards,

        VivekC