Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jja
Helper III
Helper III

Calculate total hours worked from multiple check in check outs

Hi

I have data in powerbi and i want to calculate the total hours worked by every day and then total for month for each user.

In the report i would liek to select a month and then on top row there will be each day on the left column will be employee code and in cells i want to calculate how many hours emplyee worked for that day. As you see withi na day employee can make multiple chek in and multiple check outs. Can you help me to build this?

My data in powerbi looks like this:

jja_0-1685855271338.png

 

 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@jja 

I option is, you create a table that calculates the time in minutes that the employees worked then add a new measure to sum the minutes they worked.

Here is a way to create a table:

 

Table 2 = 
ADDCOLUMNS (
    FILTER ( table1, Table1[Action] = "OUT" ),
    "Time Worked (min)",
        VAR __INTIME = Table1[Date and Time]
        VAR __EMP = Table1[Employee code]
        VAR __DATE = Table1[new_dateonly]
        VAR __OUTIME =    
            MAXX(
                FILTER (
                    ALL(table1), Table1[Employee code] = __EMP && Table1[new_dateonly] = __DATE &&  Table1[Action] = "IN"  && Table1[Date and Time] < __INTIME
                ),
                Table1[Date and Time]
            )
        VAR __TIMEWORKED = DATEDIFF( __OUTIME , __INTIME ,  MINUTE )
        RETURN
            __TIMEWORKED
)

 

2nd option is to create a measure, this may be slow depending on our data volume and the model:

Time Worked (M) Measure = 
VAR __T =
    SUMMARIZE (
        ALLSELECTED ( Table1 ),
        Table1[Employee code],
        Table1[new_dateonly],
        Table1[Date and Time]
    )
RETURN
    SUMX (
        SUMMARIZE (
            FILTER ( Table1, TABLE1[Action] = "IN" ),
            Table1[Employee code],
            Table1[new_dateonly],
            Table1[Date and Time]
        ),
        VAR __INTIME = Table1[Date and Time]
        VAR __OUTTIME =
            MAXX (
                OFFSET (
                    1,
                    __T,
                    ORDERBY ( Table1[Date and Time] ),
                    PARTITIONBY ( Table1[Employee code], Table1[new_dateonly] )
                ),
                Table1[Date and Time]
            )
        VAR __WORKED =
            DATEDIFF ( __INTIME, __OUTTIME, MINUTE )
        RETURN
            __WORKED
    )

Fowmy_0-1685913413866.png

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

3 REPLIES 3
jja
Helper III
Helper III

@Fowmy 

I used as a sollution creating a table and it works as expected untill if user did not chek OUT then the period he worked is not calculated for that day. I would like to return some indication in this case like in best case error or let's say to make a fake OUT records like 23:59 hours(user checked out at midnight) and then calculate time minutes worked. Could you asisst with that as well?

jja
Helper III
Helper III

@Fowmy 

Thank you

That works perfect!

Fowmy
Super User
Super User

@jja 

I option is, you create a table that calculates the time in minutes that the employees worked then add a new measure to sum the minutes they worked.

Here is a way to create a table:

 

Table 2 = 
ADDCOLUMNS (
    FILTER ( table1, Table1[Action] = "OUT" ),
    "Time Worked (min)",
        VAR __INTIME = Table1[Date and Time]
        VAR __EMP = Table1[Employee code]
        VAR __DATE = Table1[new_dateonly]
        VAR __OUTIME =    
            MAXX(
                FILTER (
                    ALL(table1), Table1[Employee code] = __EMP && Table1[new_dateonly] = __DATE &&  Table1[Action] = "IN"  && Table1[Date and Time] < __INTIME
                ),
                Table1[Date and Time]
            )
        VAR __TIMEWORKED = DATEDIFF( __OUTIME , __INTIME ,  MINUTE )
        RETURN
            __TIMEWORKED
)

 

2nd option is to create a measure, this may be slow depending on our data volume and the model:

Time Worked (M) Measure = 
VAR __T =
    SUMMARIZE (
        ALLSELECTED ( Table1 ),
        Table1[Employee code],
        Table1[new_dateonly],
        Table1[Date and Time]
    )
RETURN
    SUMX (
        SUMMARIZE (
            FILTER ( Table1, TABLE1[Action] = "IN" ),
            Table1[Employee code],
            Table1[new_dateonly],
            Table1[Date and Time]
        ),
        VAR __INTIME = Table1[Date and Time]
        VAR __OUTTIME =
            MAXX (
                OFFSET (
                    1,
                    __T,
                    ORDERBY ( Table1[Date and Time] ),
                    PARTITIONBY ( Table1[Employee code], Table1[new_dateonly] )
                ),
                Table1[Date and Time]
            )
        VAR __WORKED =
            DATEDIFF ( __INTIME, __OUTTIME, MINUTE )
        RETURN
            __WORKED
    )

Fowmy_0-1685913413866.png

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors