cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
johawiks Frequent Visitor
Frequent Visitor

Calculating time between check in and check out from different rows

Hi all,

 

I'm quite new to Power Query and DAX.

I have some data on Employees clocking in and out from work. I am trying to calculate the duration between clocking in and clocking out. I have not managed to figure out a way to get separate event times on same row to calculate the difference between the events. Or are there some other options to get the desired result?

 

I have about 50 employee ID:s and a year worth of data. There are several events per employee per day because there are many different events but I'm mainly interested in the time duration between In and Out on daily level.

 

Any ideas are greatly appriciated, thanks.

 

Bellow is an example of the kind of data in the table:

data_example.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-yulgu-msft
Microsoft

Re: Calculating time between check in and check out from different rows

Hi @johawiks,

 

Maybe you could try this workaround:

 

1. add an index column in Query Editor.

 

2. Create calculated columns in table view like below:

Date/Time =
'Employee-event'[EventDate] - 1
    + 'Employee-event'[EventTime]

Col1 =
CALCULATE (
    MIN ( 'Employee-event'[Index] ),
    FILTER (
        ALLEXCEPT ( 'Employee-event', 'Employee-event'[Employee ID] ),
        'Employee-event'[Index] > EARLIER ( 'Employee-event'[Index] )
            && 'Employee-event'[Event] = "Out"
    )
)

Col2 =
LOOKUPVALUE (
    'Employee-event'[Date/Time],
    'Employee-event'[Index], 'Employee-event'[Col1]
)

Col3 =
IF (
    'Employee-event'[Event] = "In",
    DATEDIFF ( 'Employee-event'[Date/Time], 'Employee-event'[Col2], MINUTE ),
    BLANK ()
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Microsoft v-yulgu-msft
Microsoft

Re: Calculating time between check in and check out from different rows

Hi @johawiks,

 

You could use below formula to create a calculated column in source table (in my test, it's 'Employee-event'):

Diff =
DATEDIFF (
    CALCULATE (
        MIN ( 'Employee-event'[EventTime] ),
        FILTER (
            ALLEXCEPT (
                'Employee-event',
                'Employee-event'[Employee ID],
                'Employee-event'[EventDate]
            ),
            'Employee-event'[Event] = "In"
        )
    ),
    CALCULATE (
        MAX ( 'Employee-event'[EventTime] ),
        FILTER (
            ALLEXCEPT (
                'Employee-event',
                'Employee-event'[Employee ID],
                'Employee-event'[EventDate]
            ),
            'Employee-event'[Event] = "Out"
        )
    ),
    MINUTE
)

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
johawiks Frequent Visitor
Frequent Visitor

Re: Calculating time between check in and check out from different rows

Hi @v-yulgu-msft,

Thank you for the reply. I tried out your suggestion and it works for most of the cases. In some cases the clocking in happens during one day and clocking out happens the next day (there are work shifts so some people are working during the night) and in these cases the formula does not return the difference. I'm guessing this is due to selecting the MIN EventTime and MAX EventTime filtered by date?

 

Best regards,

Johan

Microsoft v-yulgu-msft
Microsoft

Re: Calculating time between check in and check out from different rows

Hi @johawiks,

 

Maybe you could try this workaround:

 

1. add an index column in Query Editor.

 

2. Create calculated columns in table view like below:

Date/Time =
'Employee-event'[EventDate] - 1
    + 'Employee-event'[EventTime]

Col1 =
CALCULATE (
    MIN ( 'Employee-event'[Index] ),
    FILTER (
        ALLEXCEPT ( 'Employee-event', 'Employee-event'[Employee ID] ),
        'Employee-event'[Index] > EARLIER ( 'Employee-event'[Index] )
            && 'Employee-event'[Event] = "Out"
    )
)

Col2 =
LOOKUPVALUE (
    'Employee-event'[Date/Time],
    'Employee-event'[Index], 'Employee-event'[Col1]
)

Col3 =
IF (
    'Employee-event'[Event] = "In",
    DATEDIFF ( 'Employee-event'[Date/Time], 'Employee-event'[Col2], MINUTE ),
    BLANK ()
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

johawiks Frequent Visitor
Frequent Visitor

Re: Calculating time between check in and check out from different rows

Hi @v-yulgu-msft,

 

Thank you for your answer. I managed to get the desired result by first creating a Date/Time column from EventDate and EventTime (Event DateTime). Then I used the following measure:

 

EventDuration  =

var date = MAX(Employee-event[EventDate])

var employeeid = Employee-event[Employee ID]

var in = CALCULATE(
MIN(Employee-event[Event DateTime]); FILTER(
Employee-event; [Event]="In" && [EventDate] = date  && [Employee ID] = employeeid
)
)   var out = CALCULATE(
MAX(Employee-Event[Event DateTime]); FILTER(
Employee-event; [Event]="Out" && [EventDate] = date && [Event DateTime] > in && [Employee ID] = employeeid)
) var out2 = CALCULATE(
IF(
ISBLANK(out); CALCULATE(MIN(Employee-event[Event DateTime]); FILTER( Employee-event; [Event] = "Out" && [EventDate] = date + 1 && [Empoloyee ID] = employeeid)
);
out)
) return IF( in < out; DATEDIFF(in;out2;MINUTE); BLANK()
)

 

I will give the workaround a try too for future reference, thank you!

 

Best regards,

Johan

 

 

Highlighted
bcharlto Frequent Visitor
Frequent Visitor

Re: Calculating time between check in and check out from different rows

@v-yulgu-msft Is there a way to do this with M code in the query editer? I am handling much more data and am getting a, "There's not enough memory to complete this operation" message. 

Pseifert Frequent Visitor
Frequent Visitor

Re: Calculating time between check in and check out from different rows

@v-yulgu-msft I too am wondering this as I have hit a wall due to the memory use problem occuring

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors