topic Re: Average for N last values ? in DAX Commands and Tips

Average for N last values ?

GeoffreyLou — Wed, 30 Nov 2022 13:30:17 GMT

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?

Re: Average for N last values ?

FreemanZ — Wed, 30 Nov 2022 13:49:32 GMT

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.

Re: Average for N last values ?

GeoffreyLou — Wed, 30 Nov 2022 13:53:47 GMT

Working like a charm, many thanks !