Filter based on highest value of another table

Hi there,

I have a table with a list of companies, and another table called financials that have as keys the company and a year/quarter and as column Sales.

Companies[Name]:
Amazon
Microsoft 
Apple

Financials[Companies] Financials[Quarter] Financials[Sales]
Amazon 2020Q1 1000
Amazon 2020Q2 1250
Microsoft 2019Q4 850
Microsoft 2020Q1 1000
Amazon 2020Q3 1400
Apple 2019Q2 500

I want to have on the Companies table, a calculated row that shows me the most recent Sales Figure, as well as to which quarter it refers to, so that the Companies table would look like this:

Companies[Name] MostRecentQuarter Sales
Amazon 2020Q3 1400
Microsoft 2020Q1 1000
Apple 2019Q2 500

the FINDLASTROW doesn't work is it does not take into account the sorting of the Financials table. How can I solve this?

Hey @Oren-391d,

The good news is the FINDLASTROW function does account for sorting! Based on your screenshots I created a sample workbook using the data you provided.

Financial Table:
FinanceTable

Company Table:
Based on the screenshot you provided I'm assuming you want two separate columns for the financial data one to display the most recent quarter and another column to display the most recent sale.

=FINDLASTROW(Financials,"Financials[Company]=THISROW() ORDER BY Financials[Quarter] ASC")[Quarter]

=FINDLASTROW(Financials,"Financials[Company]=THISROW() ORDER BY Financials[Quarter] ASC")[Sales]

I hope this helps! Also just a tip make sure you do not include a space between the FINDLASTROW() function and the Column you want to display for that row for example =FINDLASTROW(Financials,"Financials[Company]=THISROW() ORDER BY Financials[Quarter] ASC")[Sales] works however if you add a space before [Sales] the formula will result in an error. Took me a while to figure that out :slight_smile:

Best Regards,

Nancy

4 Likes

Fantastic - thank you. Works Perfectly well!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.