Calculate Dynamic Rolling 4 Week Average based on multi-level category filters and using only week #

hi, Anonymous 

You could use this formula to create a measure:

Rolling 4 Weeks = 
VAR _table =
    CALCULATETABLE (
        VALUES ( 'data'[Fiscal Week Number] ),
        FILTER (
            ALLSELECTED ( 'data'[Fiscal Week Number] ),
            'data'[Fiscal Week Number]
                >= MAX ( 'data'[Fiscal Week Number] ) - 3
                && 'data'[Fiscal Week Number] <= MAX ( 'data'[Fiscal Week Number] )
        )
    )
RETURN
    IF(COUNTAX(_table,[Fiscal Week Number])=4,AVERAGEX ( _table, CALCULATE ( SUM ( 'data'[Actual] ) ) ))

Result:

Best Regards,

Lin

Hello v-lili6-msft ,

Thanks for the file. I think I know the issue. I think as soon as SKU gets introduced into the big table with everything. You won't see a Rolling 4 Weeks because there are never more than 4 weeks consecutively for SKUs.

I'll test this formula out with my real file and see if it translates over. Thanks for following up. I'll mark done as soon as I test it with my actual work file.

Thanks!