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
hwoehler
Helper I
Helper I

get last 3 weekly Values per group

Hello everybody,
I need the average of certain rows. To illustrate this, a screenshot from Excel:
RS2.PNG
I need the Close values ​​for which the following applies: weekday = 5. Based on the max date (last available date), I would like to have the last 3 values ​​(outlined in black, always corresponds to index = 0, 1 and 2 per group) so that I can then average them. This should be possible for each group A, B, C .. I think an index column (based on Weekday and Group) has to be created in order to then carry out the calculation with a measure. Anyone have an idea to implement this? Unfortunately, I am already failing in the index column. Maybe you could use topn(...) for each group? Here is the power bi file (with four rows Group, Date, Close, Weekday):  https://we.tl/t-MnjAl81PqJ
-> Example Calculation (Group A): 23,45 + 22,6+9,09/3 = ....
Thank you
Best regards, hwoehler

1 ACCEPTED SOLUTION
hwoehler
Helper I
Helper I

News: I was able to find a solution myself. If someone has an easier one, this is of course also welcome.
IndexIf(RS[Weekday]=5;RANKX (FILTER (RS;RS[Group] = EARLIER ( RS[Group] ) && RS[Weekday]=5);RS[Date];;DESC;Dense)-1;Blank())
Average_last_3_weeks
CALCULATE(AVERAGE(RS[Close]);RS[Weekday]=5;Filter(All(RS[Index]);RS[Index]<=2))
Best regards, hwoehler



View solution in original post

1 REPLY 1
hwoehler
Helper I
Helper I

News: I was able to find a solution myself. If someone has an easier one, this is of course also welcome.
IndexIf(RS[Weekday]=5;RANKX (FILTER (RS;RS[Group] = EARLIER ( RS[Group] ) && RS[Weekday]=5);RS[Date];;DESC;Dense)-1;Blank())
Average_last_3_weeks
CALCULATE(AVERAGE(RS[Close]);RS[Weekday]=5;Filter(All(RS[Index]);RS[Index]<=2))
Best regards, hwoehler



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.