Forum Discussion

iansimpson888's avatar
iansimpson888
Regular Visitor
3 years ago
Solved

creating a rolling average

Hi, i have a table displaying the weekly average attendance of a student:     It is calculated using a 'rolling' average, so: week 1 actual (05/09/2022) = 100% week 1 rolling (05/09/2022) ...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi iansimpson888 ,

     

    I suggest you to try ALL() and add [Full Name] column into filter code part.

    Weekly % Attendance (Rolling) =
AVERAGEX (
    FILTER (
        ALL ( 'powerbi_attendance_weekly_average' ),
        'powerbi_attendance_weekly_average'[Mark Date (Weeks DD/MM/YY)]
            <= MAX ( powerbi_attendance_weekly_average[Mark Date (Weeks DD/MM/YY)] )
            && 'powerbi_attendance_weekly_average'[Full Name]
                = MAX ( 'powerbi_attendance_weekly_average'[Full Name] )
    ),
    'powerbi_attendance_weekly_average'[Weekly % Attendance]
)

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.