17 week average

[PaulDenne]

have got a date set of daily timesheets

have done a measure to summarise weekly hours

but can't get the average right to exclude zero weeks in the average 

even ones with all 17 weeks populated average doesn't seem quite right

Weekly Hours = CALCULATE(SUM('Detailed - Timesheet Report'[Regular Hours]), ALLEXCEPT('Detailed - Timesheet Report', 'Detailed - Timesheet Report'[Name], 'Date'[Current week], 'Date'[Year]) )

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 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  // Ensure we filter for the current employee

            ),

            [Weekly Hours]

        ),

        ALLEXCEPT('Detailed - Timesheet Report', 'Detailed - Timesheet Report'[Name])

    )

817 over 15 weeks shoudl be average 54 not average of 60 shown

1193.51 is close but shoudl be 70.2 not 69.9

have sample PBIX but couldn't se ehow to attach it 😞

[DataNinja777]

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,

[PaulDenne]

hmm that didnt change the results so makes me wonder what period the avreage is looking at as know there was some zero weeks

[PaulDenne]

 lnk to pbix file

https://drive.google.com/file/d/1SaQmWlbC2aj3VMAg9ISJvK0e1onrNSXT/view?usp=sharing

[adudani · Accepted solution]

hi @PaulDenne ,

Within the "Filter" in your measure, maybe add  " && CurrentWeek <> "Week 0" //whatever the week number not required is.

Also, an alternative method to calculate a dynamic moving average : How to Calculate Moving Averages in Power BI - The Ultimate Guide

P.S. you could upload a google/one drive link with the pbix file if this is not resolved.

[PaulDenne]

just watched video and got it to work using rolling average

many thanks