locked
Workplace time keeping - DAX to get the earliest check-in time within a day for a specific person RRS feed

  • Question

  • Hello,

    I want to analyse what time people get to work in relation to the official start time.
    Our key fob system gives me a report with the time when people get-in and get-out (in different columns) of the building everyday. However, they can get in and out several times within a day. This means that I need to pick from the report the first check-in of everybody everyday.

    I am a beginner with PowerPivot and I am really struggling.

    I've tried with both, measures and calculated columns but without success.

    For measures I have tried as follow:

    To get the first check in have a people and date as context filter:
    Time Check-In:=MIN(TimeReport[In])

    To compare actual first check in with the official start time:
    Deviation:=TIMEVALUE("08:00:00")-TIMEVALUE([Time Check-In])

    Trying to count only the rows in for first check in that are late, however evaluates every column and hence as you can see in the screenshot, it counts 2 times late for the example person in only one day (that person wasn't late that day) 
    Late:=COUNTROWS(FILTER(TimeReport, [Deviation]<0))


    On the other hand, I've tried to add calculated columns to identify the rows that are for the first check-in of a person or the rows for late first check-in's.
    For this I have tried: =IF([in]=[Time Check-In], "First Check-in", BLANK())
    Bad result as you can see.

    I'd be pleased if you can help me. I understand this have been solved and discussed by many people in the past but I cannot find it in forums or help menus.

    Thank you!

    PS: The system does not allow me to include images until they verify my account. So I guess, I'll be able to share the screenshot soon but please try to help me in the mean time. Thanks you!

    Wednesday, March 20, 2019 7:42 PM

Answers

  • I don't think I'd actually do this in DAX, instead I'd use Power Query to load this table and Group by the employee and date columns and get a min of the check-in time and a max of the check-out time. This should generate a row per employee per day with the first check-in and last check-out all on one row.

    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Miguel-san Friday, March 22, 2019 12:52 PM
    Thursday, March 21, 2019 4:43 AM

All replies

  • I don't think I'd actually do this in DAX, instead I'd use Power Query to load this table and Group by the employee and date columns and get a min of the check-in time and a max of the check-out time. This should generate a row per employee per day with the first check-in and last check-out all on one row.

    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Miguel-san Friday, March 22, 2019 12:52 PM
    Thursday, March 21, 2019 4:43 AM
  • Thank you Darren,

    I went the wrong way completely.

    Now solved from Power Query as you said!

    Regards,

    Miguel

    Friday, March 22, 2019 12:53 PM