Moving Average Using an Index Not Working

Hi Anonymous ,

According to your new sample, here's my solution.

Create two measures.

Avg of Price = 
CALCULATE (
    AVERAGE ( 'Fruit Data'[Price] ),
    FILTER (
        ALL ( 'Fruit Data' ),
        'Fruit Data'[Purchase Date] = MAX ( 'Fruit Data'[Purchase Date] )
            && 'Fruit Data'[Product] = MAX ( 'Fruit Data'[Product] )
            && 'Fruit Data'[Delivery FY] = MAX ( 'Fruit Data'[Delivery FY] )
    )
)

Moving Avg = 
VAR Current_Date =
    MAX ( 'Fruit Data'[Purchase Date] )
VAR Number_Of_Days =
    SELECTEDVALUE ( 'Average Days'[Average Days] )
VAR Offset_Date =
    FILTER (
        ALL ( 'Calendar'[Date] ),
        RANKX (
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] < ( Current_Date ) ),
            'Calendar'[Date],
            ,
            DESC
        ) = Number_Of_Days
    )
VAR avrg =
    AVERAGEX (
        FILTER (
            ALL ( 'Fruit Data' ),
            'Fruit Data'[Delivery FY] = MAX ( 'Fruit Data'[Delivery FY] )
                && 'Fruit Data'[Product] = MAX ( 'Fruit Data'[Product] )
                && 'Fruit Data'[Purchase Date] < Current_Date
                && 'Fruit Data'[Purchase Date] >= Offset_Date
        ),
        [Avg of Price]
    )
RETURN
    avrg

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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