17 week average

Hi PaulDenne ,

To address the discrepancy in your rolling 17-week average, you’ll want to ensure that weeks with zero hours are excluded from the calculation. This can be achieved by adding a filter to remove weeks with zero hours in your AVERAGEX function. Here’s an updated approach to your measure:

Rolling17CalendarWeekAvg =
VAR CurrentDate = MAX('Date'[Date])  // Use a date column from your Date table
VAR CurrentWeek = WEEKNUM(CurrentDate, 2)  // Get the current week number
VAR CurrentEmployee = SELECTEDVALUE('Detailed - Timesheet Report'[Name])
VAR CurrentYear = YEAR(CurrentDate)  // Get the current year

RETURN
    CALCULATE(
        AVERAGEX(
            FILTER(
                ALL('Detailed - Timesheet Report'),
                'Detailed - Timesheet Report'[Year Week] >= (CurrentYear * 100 + CurrentWeek - 17) &&
                'Detailed - Timesheet Report'[Year Week] <= (CurrentYear * 100 + CurrentWeek) &&
                'Detailed - Timesheet Report'[Name] = CurrentEmployee &&
                [Weekly Hours] > 0  // Exclude weeks with zero hours
            ),
            [Weekly Hours]
        ),
        ALLEXCEPT('Detailed - Timesheet Report', 'Detailed - Timesheet Report'[Name])
    )

Best regards,