Forum Discussion

GeoffreyLou's avatar
GeoffreyLou
New Member
3 years ago
Solved

Average for N last values ?

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?

DAX
Power Pivot
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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. 

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    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.