cancel
Showing results for
Did you mean:
Highlighted
calerof Member

## Calculation of overtime per week

Hello All,

I need your help to calculate the overtime cost for a period of time on a weekly basis.

I have the following overtime worked hours database:

 PersonnelNo Name Date OvertimeWorkedHrs 4312 John 12-Jun-17 9 4312 John 13-Jun-17 9 4312 John 14-Jun-17 9 4312 John 15-Jun-17 9 4312 John 16-Jun-17 9 4312 John 17-Jun-17 9 4312 John 18-Jun-17 9 4315 Mary 12-Jun-17 8 4315 Mary 13-Jun-17 8 4315 Mary 14-Jun-17 8 4315 Mary 15-Jun-17 8 4315 Mary 16-Jun-17 8 4315 Mary 17-Jun-17 14 4315 Mary 18-Jun-17 14 4100 Joe 12-Jun-17 8 4100 Joe 13-Jun-17 8 4100 Joe 14-Jun-17 8 4100 Joe 15-Jun-17 0 4100 Joe 16-Jun-17 15 4100 Joe 17-Jun-17 12 4100 Joe 18-Jun-17 12

I calculated the overtime per week with the following formula:

OT_per_WK

```=IF(
HASONEVALUE(CalendarTable[Year])
&& HASONEVALUE(CalendarTable[WeekNumber]),
CALCULATE(
SUM(OT[OvertimeWorkedHrs]),
FILTER(
ALL( CalendarTable),
CalendarTable[Year] = VALUES(CalendarTable[Year])
&& CalendarTable[WeekNumber] = VALUES(CalendarTable[WeekNumber])
&& CalendarTable[Date] <= MAX(CalendarTable[Date])
)
),
BLANK()
)```

Additionally, I calculated the amount of overtime which is payed double, i.e. <= 9 hours per week, with the following formula:

OT_doble

```=IF(
OT[OT_per_WK] <= 9,
OT[OT_per_WK],
9
)```

And the excess of overtime are paid three-fold, then the formula to calculate the number of hours in excess of 9 overtime hours per week is the following:

OT_triple

```=IF(
OT[OT_per_WK] > 9,
OT[OT_per_WK] - 9,
0
)```

Finally, I have a table with the salary per hour of each employee:

 PersonnelNo Name SalaryperHr 4312 John 15 4315 Mary 16 4100 Joe 8

Here is where I'm struggling how to calculate the cost of ovetime per person per week and show it per month, week, or any other range of dates.

I appreciate a lot your support.

Thanks,

Fernando

1 ACCEPTED SOLUTION

Accepted Solutions v-ljerr-msft
Microsoft

## Re: Calculation of overtime per week

Hi @calerof,

According to your description above, you should be able to follow steps below to get your expected result.

1. Create a relationship between the 'OT' table and 'Salary' table with the 'PersonaelNo' column, if there isn't yet.

2. Use the formula below to add a new calculate column in the 'OT' table to calculate the overtime cost.

```Cost of Overtime =
IF (
OT[OvertimeWorkedHrs] <= 9,
OT[OvertimeWorkedHrs] * 2,
( OT[OvertimeWorkedHrs] - 9 )
* 3
+ 9 * 2
)
* RELATED ( Salary[SalaryperHr] )``` 3. Then you can use the formula below to create a new measure to calculate the overtime cost per week. ```Cost_of_OT_per_WK =
IF (
HASONEVALUE ( CalendarTable[Year] ) && HASONEVALUE ( CalendarTable[WeekNumber] ),
CALCULATE (
SUM ( OT[Cost of Overtime] ),
FILTER (
ALL ( CalendarTable ),
CalendarTable[Year] = VALUES ( CalendarTable[Year] )
&& CalendarTable[WeekNumber] = VALUES ( CalendarTable[WeekNumber] )
&& CalendarTable[Date] <= MAX ( CalendarTable[Date] )
)
),
BLANK ()
)
```

Regards v-ljerr-msft
Microsoft

## Re: Calculation of overtime per week

Hi @calerof,

According to your description above, you should be able to follow steps below to get your expected result.

1. Create a relationship between the 'OT' table and 'Salary' table with the 'PersonaelNo' column, if there isn't yet.

2. Use the formula below to add a new calculate column in the 'OT' table to calculate the overtime cost.

```Cost of Overtime =
IF (
OT[OvertimeWorkedHrs] <= 9,
OT[OvertimeWorkedHrs] * 2,
( OT[OvertimeWorkedHrs] - 9 )
* 3
+ 9 * 2
)
* RELATED ( Salary[SalaryperHr] )``` 3. Then you can use the formula below to create a new measure to calculate the overtime cost per week. ```Cost_of_OT_per_WK =
IF (
HASONEVALUE ( CalendarTable[Year] ) && HASONEVALUE ( CalendarTable[WeekNumber] ),
CALCULATE (
SUM ( OT[Cost of Overtime] ),
FILTER (
ALL ( CalendarTable ),
CalendarTable[Year] = VALUES ( CalendarTable[Year] )
&& CalendarTable[WeekNumber] = VALUES ( CalendarTable[WeekNumber] )
&& CalendarTable[Date] <= MAX ( CalendarTable[Date] )
)
),
BLANK ()
)
```

Regards

Announcements   