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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors