Average for N last values ?

[GeoffreyLou]

Hello,

I'm using Power Pivot and I need to compute the average of N last values, here N = 28 and is based on index column. Basically, I want to have the 28 last values from MAX index to (MAX index - 28)

I tired this :

AVG 4 Last week new shoppers:=CALCULATE(AVERAGE(Sweden[New shoppers]);FILTER(Sweden; MAX(Sweden[Index]) && MAX(Sweden[Index]) - 5))

But even if i change the N value (28 to 5 for example), the average is the same...

Do you have any idea?

[FreemanZ · Accepted solution]

hi @GeoffreyLou 

i tried with a simplified dataset:

 ceated a measure with such code:

AvgLast5DayNewShopper =

CALCULATE(

    AVERAGE(Sweden[NewShoppers]),

    FILTER(

        Sweden,

        Sweden[Index]>MAX(Sweden[Index]) - 5

    )

)

it worked like this:

you can change 5 to 28 in your real case. 

[GeoffreyLou]

Working like a charm, many thanks !