Calculated column for overtime by specific task type

[Richard_Halsall]

Hi, I need help in calculating an employees cumulative overtime hours by week for specific tasks

The data is as shown

I have 'half' created the OT calculated column with this DAX

OT =

VAR currentEmpID = Time[EmployeeID]

VAR currentLDW = Time[WeekNo]

VAR currentContractHours = 40

VAR OT =

CALCULATE (

SUM ( 'Time'[Duration] ),

ALL ( 'Time' ),

Time[EmployeeID] = currentEmpID

&& Time[WeekNo] = currentLDW

)

- currentContractHours

RETURN

IF ( OT > 0, OT )

The first issue is that Overtime should only be calculated where the task type is 'Working' or 'Onsite SB' not all task types as is currently happening.

The second issue is that I would like the OT to appear against the last day of the week

Any help would be much appreciated.

Thanks

[v-zhangti · Accepted solution]

Hi, @Richard_Halsall 

Please check the following methods.

OT = 
VAR currentEmpID = Time[EmployeeID]
VAR currentLDW = Time[WeekNo]
VAR currentContractHours = 40
VAR OT =
CALCULATE (
SUM ( 'Time'[Duration] ),ALL ( 'Time' ),
Time[EmployeeID] = currentEmpID
&& Time[WeekNo] = currentLDW&&'Time'[Task Type]="Working"
)+CALCULATE (
SUM ( 'Time'[Duration] ),ALL ( 'Time' ),
Time[EmployeeID] = currentEmpID
&& Time[WeekNo] = currentLDW&&'Time'[Task Type]="Onsite SB"
)
- currentContractHours
RETURN
IF ( OT > 0, OT )

Measure:

OT weekend = 
IF(SELECTEDVALUE('Time'[Start Date Site])=SELECTEDVALUE(Dates[WeekEnding]),SELECTEDVALUE('Time'[OT]),BLANK())

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[Richard_Halsall]

Many thanks just what I was after

[jaideepnema]

Hi @Richard_Halsall ,

 can you please share the pbix with the data as you have shared instead of sharing as an image ?

[Richard_Halsall]

Calculated Column Overtime Rate