Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
acg
Resolver I
Resolver I

Calculating over time hours for employees against their contract hours

‎07-25-2021 05:53 PM

Hi, this is my first ever post, yahh

 

I am drawing from a solution that has been provided some time ago (2019_ https://community.powerbi.com/t5/Desktop/Calculating-over-time-hours-for-employees/m-p/742076#M35779...), but I have some additional questions.

 

Problem discussion:

I need to calculate how much time each employee has worked overtime hours in a week (across different projects), whereby I must take the employees individual contract (the hours an employess is contracted for)  into consideration.

Some employees, might have a contact for 40 hrs, but others have a contract for only 15 hours or 45 hours. 

 

For example: 

Employee A, Project 1, Week of March 2, 2020, 15 hours

Employee A, Project 2, Week of March 2, 2020, 30 hours

Employee A has contract 40 hrs: so Employee A’s Overtime calculation is:

OT = (30 + 15) - 40 = 5

 

Employee B, Project 1, Week of March 2, 2020, 15 hours

Employee B, Project 2, Week of March 2, 2020, 30 hours

Employee B has contract 45 hrs: so Employee B’s Overtime calculation is:

OT = (30 + 15) - 45 = 0

 

If the contract hrs were always 40 hrs I could just use this calculation:

 

Overtime =

CALCULATE (

    SUM ('Timesheet Data'[Hours]),

    FILTER (

        ALL ('Timesheet Data'),

        'Timesheet Data'[Employee ID] = MIN ('Timesheet Data'[Employee ID])

            && 'Timesheet Data'[last_day_of_week] = MIN ('Timesheet Data'[last_day_of_week] )

    )

) - 40

 

My problem is that the  40 hours is not always right. So instead of - 40 I would want to subtract the individual contracted hrs (like 40 hrs in some cases and 34 ,25 or15 hrs etc in other cases ). That is, I need to be able to subtract a column (say 'contract_hrs'). But how can I do this if 'contract_hrs' is not a summable variable , and I have a CALCULATE in the first part?

 

I would have thought I neded something like: 

 

Overtime2 =

CALCULATE (

    SUM ('Timesheet Data'[Hours]),

    FILTER (

        ALL ('Timesheet Data'),

        'Timesheet Data'[Employee ID] = MIN ('Timesheet Data'[Employee ID])

            && 'Timesheet Data'[last_day_of_week] = MIN ('Timesheet Data'[last_day_of_week] )

    )

)

- 

CALCULATE (

     ('Timesheet Data'[ contract_hrs]),

    FILTER (

        ALL ('Timesheet Data'),

        'Timesheet Data'[Employee ID] = MIN ('Timesheet Data'[Employee ID])

            && 'Timesheet Data'[last_day_of_week] = MIN ('Timesheet Data'[last_day_of_week] )

 

But CALCULATE demands an aggregated field. So how do I get arround it?

How can I achieve this?

 

The next step will be that I need a calculation that tells me when that overtime is over 10% of what an employee should be working.  Any ideas are welcome. 

 

 

 

Message 1 of 8
1,238 Views
0
3 ACCEPTED SOLUTIONS
ERD
Community Champion
Community Champion
In response to acg

‎07-26-2021 02:25 AM

@acg , you can try these measures:

 

OT =
VAR currentEmpID = MAX ( T[Employee ID] )
VAR currentLDW = MAX ( T[last_day_of_week] )
VAR currentContractHours = MAX ( T[Contract_Hours] )
VAR OT =
    CALCULATE (
        SUM ( T[Hours] ),
        FILTER (
            ALL ( T[Employee ID], T[last_day_of_week] ),
            T[Employee ID] = currentEmpID
                && T[last_day_of_week] = currentLDW
        )
    ) - currentContractHours
RETURN
    IF ( OT > 0, OT )
OT>10% = 
VAR currentContractHours = MAX ( T[Contract_Hours] )
VAR OT = [OT] / currentContractHours
RETURN
    IF ( OT > 0.1, "yes" )

ERD_0-1627291531971.png

 

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!

View solution in original post

Message 4 of 8
1,161 Views
acg
Resolver I
Resolver I
In response to acg

‎07-27-2021 03:11 AM

ok I worked it out :-):

 

Percentage = ('T'[Overtime] / MAX ( 'T'[Contract_Hours] )*100)

View solution in original post

Message 8 of 8
1,111 Views
0
ERD
Community Champion
Community Champion
In response to acg

‎07-27-2021 04:30 AM

@acg ,

you can just 

RETURN 
OT

and show as percentage

ERD_0-1627385414776.png

 

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!

View solution in original post

Message 6 of 8
1,100 Views
7 REPLIES 7
acg
Resolver I
Resolver I

‎07-26-2021 01:53 AM

THe atrtached sampe data shows one week of ythe data, whereby Employee_ID can have worked accross many different projects reflected in different hours. Hence I am taking a SUM of the hours in the CAlculation above. But the Contract_ hours remain the same -  as one always has the same contracted hours for a chosen job. 

Message 7 of 8
1,168 Views
0
acg
Resolver I
Resolver I
In response to acg

‎07-27-2021 03:11 AM

ok I worked it out :-):

 

Percentage = ('T'[Overtime] / MAX ( 'T'[Contract_Hours] )*100)
Message 8 of 8
1,112 Views
0
ERD
Community Champion
Community Champion

‎07-25-2021 11:14 PM

@acg , please, provide a data sample in a table format.

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!

Message 2 of 8
1,183 Views
acg
Resolver I
Resolver I
In response to ERD

‎07-26-2021 01:48 AM
last_day_of_weekEmployee IDHoursContract_Hours
20/06/2021220.540
20/06/202122140
20/06/202122240
20/06/202122440
20/06/202122940
20/06/202122840
20/06/202143136
20/06/202143936
20/06/2021560.540
20/06/202156140
20/06/2021561.540
20/06/202156240
20/06/202156340
20/06/202156440
20/06/202156640
20/06/2021566.540
20/06/2021770.540
20/06/2021770.7540
20/06/202177140
20/06/2021771.2540
20/06/2021771.540
20/06/202177240
20/06/2021772.540
20/06/2021774.540
20/06/202177640
20/06/202177740
20/06/2021340.540
20/06/202134140
20/06/2021341.540
20/06/202134240
20/06/2021342.540
20/06/202134340
20/06/2021343.540
20/06/202134740
20/06/202134840
20/06/202134940
20/06/202121935
20/06/2021211135
20/06/202121535
20/06/202121935
20/06/202121835
20/06/202121835
20/06/202121935
20/06/202121835
20/06/202121935
18/06/2021331037.5
19/06/202133937.5
20/06/202133837.5
20/06/202133837.5
20/06/2021331037.5
20/06/202127840
20/06/202127940
20/06/202127540
20/06/202127340
20/06/202127640
20/06/202127240
20/06/202127340
20/06/202127440
20/06/202127840
20/06/202129940
20/06/2021291140
20/06/2021291040
20/06/202129940
20/06/2021291040
20/06/2021291140

@ ERD , Attached is a sample data. Is there a better way to save it?

Message 3 of 8
1,171 Views
0
ERD
Community Champion
Community Champion
In response to acg

‎07-26-2021 02:25 AM

@acg , you can try these measures:

 

OT =
VAR currentEmpID = MAX ( T[Employee ID] )
VAR currentLDW = MAX ( T[last_day_of_week] )
VAR currentContractHours = MAX ( T[Contract_Hours] )
VAR OT =
    CALCULATE (
        SUM ( T[Hours] ),
        FILTER (
            ALL ( T[Employee ID], T[last_day_of_week] ),
            T[Employee ID] = currentEmpID
                && T[last_day_of_week] = currentLDW
        )
    ) - currentContractHours
RETURN
    IF ( OT > 0, OT )
OT>10% = 
VAR currentContractHours = MAX ( T[Contract_Hours] )
VAR OT = [OT] / currentContractHours
RETURN
    IF ( OT > 0.1, "yes" )

ERD_0-1627291531971.png

 

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!

Message 4 of 8
1,162 Views
acg
Resolver I
Resolver I
In response to ERD

‎07-27-2021 01:14 AM

Hi ERD, what is needd though to get simply the percentage for all values? Why can I not put:

 

VAR OT = (([OT] / currentContractHours) *100)

 

is this as we have defined VAR? Would that need to be resolved with CALCULATE ? 

I tried: 

OT =
VAR currentContractHours = MAX ( 'T'[Work_Week_Hours])
VAR OT =
CALCULATE (('T'[Overtime] / currentContractHours) *100)
 
What is missing?  I do not quite understand how this works.

 

Message 5 of 8
1,123 Views
0
ERD
Community Champion
Community Champion
In response to acg

‎07-27-2021 04:30 AM

@acg ,

you can just 

RETURN 
OT

and show as percentage

ERD_0-1627385414776.png

 

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!

Message 6 of 8
1,101 Views

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

View All