Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |