Picking the Last Non-empty Row

Solved3.40K viewsFormulas and Functions

I have 3 dimension matrix with Year, Data, Company categories.

Year is simply 2000, 2001, 2002 etc. The Data category has some data input rows and some calculated rows.
For example, one calculated row is the average for the previous 5 years of an input row.
This part works fine using running average techniques.

I want to have a second, summary, matrix that captures some of the calculated rows, e.g. the latest 5 year average.

The tricky part is I only want it for the last year with actual data in the Data input cells. Not all companies have data for the most recent year(s). So I want to see the last known 5 year average for each company.

For example, Company A might have data for all years, but Company B doesn’t yet have data for 2011 & 2012. Company C might not have 2009-2012.

This formula partially works.
Summary::Latest Ave = DataTable::5YrAve:Year[Last]

This works for Company A. But it picks up #Div/0 error for Company B & C because the last year (2012) doesn’t yet have proper data to calculate an average.

Anyone know how to do this?



In the attached model, I am using the “last” function to achieve this.

Latest Ave = last(Data Table::’5YrAve’:Year, 1)

For most accurate reporting, you should make sure that you are only calculating a 5 year average for years that have five years worth of data.


Thanks for the reply Valerie.

The last function does work. Although I did have to change my approach a bit to accomodate.

I’m not running QM 5 and so was unable to load your model.

Latest Questions