Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
calerof
Impactful Individual
Impactful Individual

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
v-ljerr-msft
Employee
Employee

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
v-ljerr-msft
Employee
Employee

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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