cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Richard_Halsall
Helper I
Helper I

Calculated column for overtime by specific task type

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

The data is as shown

Richard_Halsall_0-1646321499001.png

 

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

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

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())

vzhangti_0-1646726989337.png

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.

View solution in original post

4 REPLIES 4
v-zhangti
Community Support
Community Support

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())

vzhangti_0-1646726989337.png

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.

Many thanks just what I was after

jaideepnema
Super User
Super User

Hi @Richard_Halsall ,

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors