Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
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.
Many thanks just what I was after
Hi @Richard_Halsall ,
can you please share the pbix with the data as you have shared instead of sharing as an image ?
User | Count |
---|---|
66 | |
46 | |
20 | |
19 | |
15 |
User | Count |
---|---|
121 | |
41 | |
38 | |
28 | |
23 |