Forum Discussion

PaulDenne's avatar
PaulDenne
Helper I
1 year ago
Solved

17 week average

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 😞

Need Help

5 Replies

    • PaulDenne's avatar
      PaulDenne
      Helper I
      1 year ago

      just watched video and got it to work using rolling average

       

      many thanks

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    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's avatar
      PaulDenne
      Helper I
      1 year ago

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