Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
GeoffreyLou
New Member

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?

1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

hi @GeoffreyLou 

i tried with a simplified dataset:

FreemanZ_0-1669816018805.png

 ceated a measure with such code:

AvgLast5DayNewShopper =
CALCULATE(
    AVERAGE(Sweden[NewShoppers]),
    FILTER(
        Sweden,
        Sweden[Index]>MAX(Sweden[Index]) - 5
    )
)
it worked like this:
FreemanZ_1-1669816077302.png

 

you can change 5 to 28 in your real case. 

View solution in original post

2 REPLIES 2
FreemanZ
Super User
Super User

hi @GeoffreyLou 

i tried with a simplified dataset:

FreemanZ_0-1669816018805.png

 ceated a measure with such code:

AvgLast5DayNewShopper =
CALCULATE(
    AVERAGE(Sweden[NewShoppers]),
    FILTER(
        Sweden,
        Sweden[Index]>MAX(Sweden[Index]) - 5
    )
)
it worked like this:
FreemanZ_1-1669816077302.png

 

you can change 5 to 28 in your real case. 

Working like a charm, many thanks ! 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors