Pulling a Rolling Average - Dynamic Time

Hi kjanderson2 

Create calculated columns

year-week = FORMAT([date],"yyyy-ww")

Create measures

sum per week =
CALCULATE (
    SUM ( Sheet3[value] ),
    FILTER (
        ALLSELECTED ( Sheet3 ),
        Sheet3[item]
            = MAX ( Sheet3[item] )
            && Sheet3[year-week]
                = MAX ( Sheet3[year-week] )
    )
)

sum last 6 weeks =
CALCULATE (
    SUM ( Sheet3[value] ),
    FILTER (
        ALLSELECTED ( Sheet3 ),
        DATEDIFF (
            Sheet3[date],
            TODAY (),
            WEEK
        ) < 6
            && Sheet3[item]
                = MAX ( Sheet3[item] )
    )
)


count_last6weeks =
CALCULATE (
    DISTINCTCOUNT ( Sheet3[year-week] ),
    FILTER (
        ALLSELECTED ( Sheet3 ),
        DATEDIFF (
            Sheet3[date],
            TODAY (),
            WEEK
        ) < 6
            && Sheet3[item]
                = MAX ( Sheet3[item] )
            && [sum per week] <> 0
    )
)

average = [sum last 6 weeks]/[count_last6weeks]

If there are no missing data

If there is missing data,

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.