cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
WiserGuy Frequent Visitor
Frequent Visitor

Combining date and week sum measure

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

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Combining date and week sum measure

@WiserGuy ,

 

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 ) )
            )
        )
    )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Combining date and week sum measure

@WiserGuy ,

 

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 ) )
            )
        )
    )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
WiserGuy Frequent Visitor
Frequent Visitor

Re: Combining date and week sum measure

Brilliant - thanks!