Forum Discussion

rajasekar_o's avatar
rajasekar_o
Icon for Helper V rankHelper V
2 years ago
Solved

Attendance out time

 

hi team
i have daily attendance sheet , if type 0 is intime 1 is outtime
i need min of out time and the aboue data   21.11.23 have intime but no outtime the the employee out from office nextday morning (22.11.23,2.30.00 am) how to make this time as 21.11.23 date log out time 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi rajasekar_o,

    You can try to use the following calculated table formula to create a new table with the current table records based on empname, Date group and correspond in/out time values.

    newTable =
SUMMARIZE (
    ADDCOLUMNS ( Table1, "Date", DATEVALUE ( [Datetime] ) ),
    [empName],
    [Date],
    "In Time",
        CALCULATE (
            TIMEVALUE ( MIN ( Table1[Datetime] ) ),
            FILTER (
                ALLSELECTED ( Table1 ),
                DATEVALUE ( [Datetime] ) = [Date]
                    && [Type] = 0
            ),
            VALUES ( Table1[empName] )
        ),
    "Out Time",
        CALCULATE (
            TIMEVALUE ( MAX ( Table1[Datetime] ) ),
            FILTER (
                ALLSELECTED ( Table1 ),
                DATEVALUE ( [Datetime] ) = [Date]
                    && [Type] = 1
            ),
            VALUES ( Table1[empName] )
        )
)

    Regards,

    Xiaoxin Sheng

8 Replies

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    2 years ago

    Why does 20/11 have 2 consecutive in date/times?

    Why does 22/11 have  2 consquentive out dates/times?

     

    • rajasekar_o's avatar
      rajasekar_o
      Icon for Helper V rankHelper V
      2 years ago

      which one is minimum timeon that then  take that is out time 

    • rajasekar_o's avatar
      rajasekar_o
      Icon for Helper V rankHelper V
      2 years ago

      expect out put i need dax for  in time  and outtime 

       

      • speedramps's avatar
        speedramps
        Icon for Super User rankSuper User
        2 years ago
        Hi  rajasekar_o 
        Pleaase accept my solution which does exactly what you asked for.
        It will even work for multiple employees.
        Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,

        So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

        One question per ticket please. If you need to change or extend your request then please raise a new ticket.

        You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

        If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

        Please now click the [accept as solution] and the thumbs up button.  Thank you.

        PREVIEW
         
         
         
    • rajasekar_o's avatar
      rajasekar_o
      Icon for Helper V rankHelper V
      2 years ago

      expect out put i need dax for  in time  and outtime 

       

       

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    2 years ago

    Click here to down load an PBIX solution
    https://1drv.ms/u/s!AgMTUY3Uvq3bg8t78PEZUNjHCUFnCA?e=piEpVg 

     

    How is works ...

    Clockout = 
VAR clockin =  SELECTEDVALUE(yourtable[date/time])      // get the clock in time
RETURN
CALCULATE(
    MIN(yourtable[date/time]),                          // get the min clock out time
    ALLEXCEPT(yourtable,yourtable[empname]),            // for the employee
    yourtable[type] = 1,                                // for chech out times only
    yourtable[date/time] >= clockin                     // where the chcekout time is greater or equals to the check in time
    )

     

    Thanks for the clear description of the problem with example data. I wish everyone did that!

    Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
    This solution works and does exactly what you asked.
    So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

    One question per ticket please. If you need to change or extend your request then please raise a new ticket.

    You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

    If you quote speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

    Please now click the [accept as solution] and the thumbs up button.  Thank you.