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'm struggling to find out a way to summarize overtime hours based on the regional definition of overtime which is:
More than 9 hours per day -or- More than 40 hours per week
The following measure gives me the data I need if I only had to think about days:
OvertimeMeasureDay = CALCULATE( SUMX( SUMMARIZE( 'TimesheetLines'; 'TimesheetLines'[EmployeeNo]; 'TimesheetLines'[Date]; "OverTimeHoursDay"; SUM('TimesheetLines'[WorkHoursCalc]) ); IF([OverTimeHoursDay] > 9;[OverTimeHoursDay] - 9; 0 ) ) )
And the following works fine to find weeks:
OvertimeMeasureWeek = CALCULATE( SUMX( SUMMARIZE( 'TimesheetLines'; 'TimesheetLines'[EmployeeNo]; 'TimesheetLines'[WeekNo]; "OverTimeHoursWeek"; SUM('TimesheetLines'[WorkHoursCalc]) ); IF([OverTimeHoursWeek] > 40;[OverTimeHoursWeek] - 40; 0 ) ) )
My problem is to combine these two measures to get one sum, using day measure when that gives the highest result, and week measure when week gives the highest result. Or can someone think of a different way of doing this? I need to be able to filter this down to date level.
A couple of scenarios:
-10 hour a day and off rest of week - 1 hour overtime even though week is not 40 hours
-12 hours for 3 days and 5 hour for 2 days. - 9 hour overtime even though week is only 46 hours
-8.5 hours for 5 days - 2.5 hour overtime as week is over 40 hours
Solved! Go to Solution.
@Anonymous ,
You may refer to the measure below.
Measure = SUMX ( SUMMARIZE ( 'TimesheetLines', 'TimesheetLines'[EmployeeNo], 'TimesheetLines'[WeekNo] ), CALCULATE ( MAX ( SUM ( 'TimesheetLines'[WorkHoursCalc] ) - 40, SUMX ( SUMMARIZE ( 'TimesheetLines', 'TimesheetLines'[Date] ), CALCULATE ( MAX ( SUM ( 'TimesheetLines'[WorkHoursCalc] ) - 9, 0 ) ) ) ) ) )
@Anonymous ,
You may refer to the measure below.
Measure = SUMX ( SUMMARIZE ( 'TimesheetLines', 'TimesheetLines'[EmployeeNo], 'TimesheetLines'[WeekNo] ), CALCULATE ( MAX ( SUM ( 'TimesheetLines'[WorkHoursCalc] ) - 40, SUMX ( SUMMARIZE ( 'TimesheetLines', 'TimesheetLines'[Date] ), CALCULATE ( MAX ( SUM ( 'TimesheetLines'[WorkHoursCalc] ) - 9, 0 ) ) ) ) ) )
Brilliant - thanks!
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |