topic Calculating over time hours for employees against their contract hours in DAX Commands and Tips

Calculating over time hours for employees against their contract hours

acg — Mon, 26 Jul 2021 00:53:51 GMT

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#M357799), 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.

Re: Calculating over time hours for employees against their contract hours

ERD — Mon, 26 Jul 2021 06:14:14 GMT

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.

Re: Calculating over time hours for employees against their contract hours

acg — Mon, 26 Jul 2021 08:48:56 GMT

last_day_of_week	Employee ID	Hours	Contract_Hours
20/06/2021	22	0.5	40
20/06/2021	22	1	40
20/06/2021	22	2	40
20/06/2021	22	4	40
20/06/2021	22	9	40
20/06/2021	22	8	40
20/06/2021	43	1	36
20/06/2021	43	9	36
20/06/2021	56	0.5	40
20/06/2021	56	1	40
20/06/2021	56	1.5	40
20/06/2021	56	2	40
20/06/2021	56	3	40
20/06/2021	56	4	40
20/06/2021	56	6	40
20/06/2021	56	6.5	40
20/06/2021	77	0.5	40
20/06/2021	77	0.75	40
20/06/2021	77	1	40
20/06/2021	77	1.25	40
20/06/2021	77	1.5	40
20/06/2021	77	2	40
20/06/2021	77	2.5	40
20/06/2021	77	4.5	40
20/06/2021	77	6	40
20/06/2021	77	7	40
20/06/2021	34	0.5	40
20/06/2021	34	1	40
20/06/2021	34	1.5	40
20/06/2021	34	2	40
20/06/2021	34	2.5	40
20/06/2021	34	3	40
20/06/2021	34	3.5	40
20/06/2021	34	7	40
20/06/2021	34	8	40
20/06/2021	34	9	40
20/06/2021	21	9	35
20/06/2021	21	11	35
20/06/2021	21	5	35
20/06/2021	21	9	35
20/06/2021	21	8	35
20/06/2021	21	8	35
20/06/2021	21	9	35
20/06/2021	21	8	35
20/06/2021	21	9	35
18/06/2021	33	10	37.5
19/06/2021	33	9	37.5
20/06/2021	33	8	37.5
20/06/2021	33	8	37.5
20/06/2021	33	10	37.5
20/06/2021	27	8	40
20/06/2021	27	9	40
20/06/2021	27	5	40
20/06/2021	27	3	40
20/06/2021	27	6	40
20/06/2021	27	2	40
20/06/2021	27	3	40
20/06/2021	27	4	40
20/06/2021	27	8	40
20/06/2021	29	9	40
20/06/2021	29	11	40
20/06/2021	29	10	40
20/06/2021	29	9	40
20/06/2021	29	10	40
20/06/2021	29	11	40

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

Re: Calculating over time hours for employees against their contract hours

acg — Mon, 26 Jul 2021 08:53:36 GMT

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.

Re: Calculating over time hours for employees against their contract hours

ERD — Mon, 26 Jul 2021 09:25:41 GMT

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" )

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

Re: Calculating over time hours for employees against their contract hours

acg — Tue, 27 Jul 2021 08:14:19 GMT

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.

Re: Calculating over time hours for employees against their contract hours

acg — Tue, 27 Jul 2021 10:11:33 GMT

ok I worked it out :-):

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

Re: Calculating over time hours for employees against their contract hours

ERD — Tue, 27 Jul 2021 11:30:22 GMT

acg ,

you can just

RETURN OT

and show as percentage

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