Last week at my Sydney training course, one of the students asked me a question about LASTNONBLANK. This reminded me what a trickily deceptive function LASTNONBLANK is. It sounds like an easy DAX formula to understand, right? It just finds the last non blank value in a column – easy right? Well it is a little bit trickier than that, and I am going to explain it all in this post today.
LASTNONBLANK has a sibling FIRSTNONBLANK that operates in exactly the same way but in reverse. Given the behaviour is the same, I won’t cover FIRSTNONBLANK at all, as you can work it out after reading about LASTNONBLANK.
The syntax of LASTNONBLANK is as follows.
It takes 2 parameters, 1) a column and 2) an expression. In fact you can also pass a single column table as the first parameter in as well. This could be useful for example if you wanted to create your own table on the fly using a function that returns a table, and then use that as the first parameter.
LASTNONBLANK is an Iterator
It is not immediately obvious, but LASTNONBLANK is actually an iterator, It iterates over the column specified in the first parameter and finds (unsurprisingly) the last value in that column that is not blank. (Technically the engine carries out this task in reverse natural sort order for efficiency).
But what is that pesky second parameter?
The first thing people normally find confusing about LASTNONBLANK is that pesky second parameter. If all you want to do is find the last non blank value in a column, why do you need this second parameter? Well in short, you don’t “need” it in that use case (except that it is mandatory). But having this parameter makes the formula much more powerful, so it is good to have it as an option. It is designed to allow you to write a DAX expression such as a measure to test for some condition or value. Ideally (in my view) Microsoft should have made this an optional parameter, but that is not what we got. But there is an easy work around that allows you to make this parameter optional – just use the value 1 as the second parameter, like this.
myMeasure = LASTNONBLANK(Table[Column], 1)
LASTNONBLANK iterates through the Table[Column] and then checks to see if the second parameter has a value. The number 1 always has a value of course, so placing 1 as the second parameter has the same effect as just ignoring this parameter. Instead the formula will just return the last non blank value it finds in the column.
LASTNONBLANK actually returns a TABLE
Another thing that is not immediately obvious is that LASTNONBLANK actually returns a TABLE. It is a special single column, single row table that by definition can only have 1 possible value (because it has only 1 row and 1 column). One feature of this single row, single column table is that you can use it as both a scalar value or a Table in DAX. More on that later too.
My test data for this blog post is monthly bank account balances for 2 fake bank accounts. Measures that calculate account balances in DAX are often described as “semi-additive measures”, because you can’t just add up the values from each month to get the current balance – you need to find the latest balance to work out how much you have. This type of data is a prime candidate to use the LASTNONBLANK formula, because LASTNONBLANK does exactly what we need to work with semi-additive measures.
I have set up the following data with some interesting features to demonstrate the behaviour of LASTNONBLANK.
It is easiest to see what I have done with this test data in the pivot table below. I have written the following measure to demonstrate the point.
Sum of Balance = SUM(Data[Balance])
Note the above measure is a test measure only to help with this blog – it doesn’t really make any sense as is, but it is useful for describing what is happening. When the above measure is placed in a pivot table, it looks like this (shown below).
Note a couple of things about this data.
- Kathy’s account doesn’t have a result for May
- Matt’s account doesn’t have a result for July
- The maximum value for Kathy’s account is in June (the previous month’s data load)
- The maximum value for Matt’s account is back in May.
I have set the data up this way to demonstrate the behaviour of LASTNONBLANK. I often talk about how important it is to set up test data that will flush out issues when you write your formulas. This is a good example of that, and it will make more sense as you read on.
Last value in a Column
Now the objective is to use LASTNONBLANK to find the last value in a column.
Consider the following formula.
Last Non Blank Date = LASTNONBLANK(Data[Date], 1)
This formula finds the last date in the data table (note it is not the last date in the Calendar table, but the data table).
The following pivot table has the Month name from the calendar table and the account names on pivot table columns.
Note that the formula correctly indicates that May data is missing for Kathy, and July data is missing for Matt. Also note that it correctly gives the last date in the Grand Total of the pivot table. (You would also get the same result as above if you used LASTDATE instead of LASTNONBLANK). I have only used LASTNONBLANK here to demonstrate the behaviour.
LASTNONBLANK operates over a sorted column
The [Last Non Blank Date] measure above hides some complexities about LASTNONBLANK. You might expect that LASTNONBLANK finds the last value in the column, but that is not how it works. It actually finds the last value in a sorted column using the natural sort behaviour of the column data type. In the case of a date column (like shown above), then everything is sweet – the natural sort order is also the order we normally load the data (chronological order). But in the case of the balance column, the natural sort order of a numeric column is numerical sort order, not the order the data is loaded (as you can see in the following pivot)
I have written another test measure as follows
LastNonBlank Balance = LASTNONBLANK(Data[Balance], 1)
When placed in the pivot table, you get the following behaviour.
In the above example, you may expect the Grand Total row to return the values 2,125 for Kathy and 1,557 for Matt as these are the last values you loaded, but that is not how it works. When looking at the individual rows in the pivot table, it all works fine – it correctly finds the last balance for each month. This measure works correctly on the rows in the pivot table because the rows provide “initial filter context” prior to the measure being evaluated. So there is only ever 1 row in the data table at the time the measure is evaluated, and that is why it works in this case. But in the Grand Total row in the pivot table, there is no initial filter context applied to the date, hence all values in the column are iterated over by LASTNONBLANK. The iteration operates over a sorted version of the balance column (the natural sort order of the column, which is numeric in this case). Once the balance column is sorted, then 2,200 will be the last value in the column for Kathy, and 1,806 will be the last value in the column for Matt, and that is the result that is returned.
LASTNONBLANK as a TABLE
In this next test measure, I have used LASTNONBLANK as a filter input into a CALCULATE function
Latest Balance with 1 = CALCULATE( SUM(Data[Balance]), LASTNONBLANK('Calendar'[Date], 1) )
You should remember that CALCULATE can use a simple filter such as Table[Column] = “some value” or it can take an advanced filter where you must pass a TABLE as the filter. The measure above therefore suggests that LASTNONBLANK must be a table, not a scalar value. This theory can be tested by firing up DAX Studio and executing the LASTNONBLANK portion of the formula as a query. DAX Studio ONLY ever returns tables, it can’t return scalar values, so if the query works, it confirms that LASTNONBLANK returns a table. Note: you can read more about using DAX Studio as a tool to help you learn DAX at this blog post here.
You can see above that LASTNONBLANK returns a single column, single row table that contains a single value. It is a special table because it can also be used as a scalar value, in the same way that you can use VALUES() as a scalar value if there is only a single row returned. In the case of the measure [Latest Balance with 1] above, I am using LASTNONBLANK as a table parameter filter inside CALCULATE.
But how does this table act as a filter?
One feature of temporary tables in DAX is they retain a link to the data model, and filter propagation will work between the temporary table and the rest of the data model. You should visualise this in your mind like the image below. Imagine a new table that has spawned into the data model and has a relationship to the table where it came from (in this case it is the data table). The filter propagation flows from the 1 side of the relationship (temporary table imagined as shown in 1 below) to the many side of the relationship (table 2 below).
So you can see above that the LASTNONBLANK function produces a table that then filters the data model based on the single value it returns in the table.
When I put this measure in a pivot table, I get this result
Note that the measure is correctly returning the last value for the account “Kathy” but it is returning a blank for account “Matt”. Technically this is correct because the calendar has dates in July and there is no entry for Matt for July, so the last value is blank.
Enter the second parameter
Now it is time to change the second parameter with the following measure.
Latest Balance with formula = CALCULATE( SUM(Data[Balance]), LASTNONBLANK('Calendar'[Date], [Sum of Balance]) )
When this last measure is added to a pivot table, it works as desired, correctly returning the last non blank value from the data table based on the chronological order of the data.
This new LASTNONBLANK function measure returns the last non blank value it finds in the column provided it also returns a non-blank result for the measure [Sum of Balance].
It is worth pointing out here that the ONLY REASON this formula works is because the measure [Sum of Balance] has an implicit CALCULATE wrapped around it – you can’t see it, but it is there. LASTNONBLANK is an iterator, and like all iterators, it has a row context but doesn’t have a filter context. Therefore the following formula will not work
Latest Balance with formula = CALCULATE( SUM(Data[Balance]), LASTNONBLANK('Calendar'[Date], SUM(Data[Balance])) )
But this next measure does work (this next formula is the equivalent of the one that uses [Sum of Balance] because [Sum of Balance] has an implicit CALCULATE.
Latest Balance with formula = CALCULATE( SUM(Data[Balance]), LASTNONBLANK('Calendar'[Date], CALCULATE( SUM(Data[Balance]) ) ) )
I cover evaluation context and context transition in detail in my book, Supercharge Power BI. If you want to have guided learning of the complex DAX concepts, you can enroll to my Power BI online training.