cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
calerof Member
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:

 

PersonnelNoNameDateOvertimeWorkedHrs
4312John12-Jun-179
4312John13-Jun-179
4312John14-Jun-179
4312John15-Jun-179
4312John16-Jun-179
4312John17-Jun-179
4312John18-Jun-179
4315Mary12-Jun-178
4315Mary13-Jun-178
4315Mary14-Jun-178
4315Mary15-Jun-178
4315Mary16-Jun-178
4315Mary17-Jun-1714
4315Mary18-Jun-1714
4100Joe12-Jun-178
4100Joe13-Jun-178
4100Joe14-Jun-178
4100Joe15-Jun-170
4100Joe16-Jun-1715
4100Joe17-Jun-1712
4100Joe18-Jun-1712

 

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:

 

PersonnelNoNameSalaryperHr
4312John15
4315Mary16
4100Joe8

 

 

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
Microsoft 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] )

c2.PNG

 

3. Then you can use the formula below to create a new measure to calculate the overtime cost per week. Smiley Happy

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

View solution in original post

1 REPLY 1
Microsoft 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] )

c2.PNG

 

3. Then you can use the formula below to create a new measure to calculate the overtime cost per week. Smiley Happy

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

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,502)