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?
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:
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]
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