Forum Discussion
Helper IVCalculated 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
I have 'half' created the OT calculated column with this DAX
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
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.
4 Replies
- jaideepnema
Solution Sage
Hi Richard_Halsall ,
can you please share the pbix with the data as you have shared instead of sharing as an image ?
v-zhangtiCommunity 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())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_HalsallMany thanks just what I was after
