Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |