LASTNONBLANK Explained

Level: Intermediate

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.

Syntax

The syntax of LASTNONBLANK is as follows.

LASTNONBLANK(Table[Column],<expression>)

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.

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.

Test Data

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.

image

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.

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).

image

Note a couple of things about this data.

  1. Kathy’s account doesn’t have a result for May
  2. Matt’s account doesn’t have a result for July
  3. The maximum value for Kathy’s account is in June (the previous month’s data load)
  4. 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.

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.

image

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

When placed in the pivot table, you get the following behaviour

image

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

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.

image

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).

image

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

image

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.

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.

image

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

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

I cover evaluation context and context transition in detail in my book, Learn to Write DAX.

Further reading/references

Here are some good links that helped me learn how LASTNONBLANK works, and how it can be used/leveraged if you are interested in doing some more reading on this topic.

http://www.sqlbi.com/articles/semi-additive-measures-in-dax/

https://www.sqlbi.com/articles/alternative-use-of-firstnonblank-and-lastnonblank/

http://www.powerpivotpro.com/2012/06/top-selling-product-using-firstnonblank/

Share?

Comments

  1. I think one more thing to consider explicitly adding is that since LASTNONBLANK (and sibling FIRSTNONBLANK ) is a table function, it does perform context transition of external row context and so need to be aware of that in certain situations. For example, My_Measure = CALCULATE( [Sum Measure], FILTER ( ALL (Calendar ), Calendar[Date] = LASTNONBLANK ( Calendar[Date],1) ) ) doesn’t return the [Sum Measure] for the last date in the Calendar, it will return [Sum Measure] for all Dates. This is because of the context transition performed by LASTNONBLANK inside the FILTER’s Calendar[Date] iteration. LASTNONBLANK ( Calendar[Date],1) will always return the currently iterated date and hence comparison always true and hence all Calendar rows returned. I’m sure you know this but food for thought to include in the blog as a potential “gotcha” for other folks when writing Dax.

  2. I’ve used both FirstNonBlank & LastNonBlank as semi-additive measures to for a powerpivot model and most everything works as expected.
    the only area that I run into issues is with items that are currently not in inventory. for example item A has inventory in months 1,2,3. then in month 4,5,6 there is no inventory,(and no inventory record in the model) so when I use “LASTNONBLANK”, the data I get is for month 3 as that is the “LASTNONBLANK” month with data. is there a way to return a blank or zero if an item hand inventory in the X previous months? but no data in the last month with out having to manually add a zero quantity for months 4,5,6 in the source data.

    • Are you saying that when a count is done, all products are counted, and if there is no count record, then it is out of stock? If so, use lastdate to find the date of the last count then use that as an input to a calculate. Something like
      =calculate(sum(table[value]),lastdate(table[date]))

  3. Hi Matt,
    thanks for your response. yes, that’s exactly what i’m saying. the item is out of stock.
    when I use the above calculate formula I still get the data returned for Jan. the item has been out of stock after Jan.
    the following formula’s both return Jan data:
    LastBalance = CALCULATE(SUM(MntEndInv[QOH]),LASTNONBLANK(dimDate[Date],CALCULATE(SUM(MntEndInv[QOH]))))
    LastBalance2 = CALCULATE(SUM(MntEndInv[QOH]),LASTDATE(dimDate[Date])) (also tried using the date from the fact table, same result.)

  4. Thanks Matt – very clear !!
    Just a precision : LastNonBlank performs the iteration in the reverse order.

    For example, let’s imagine that we have October 2016 in the filter context. So days are sorted from 01 October 2016 to 31 October 2016. LastNonblank starts iterating from 31 October 2016 !
    So if you have a balance for that day, Lastnonblank only iterated once and returns the value (as a table format as you mention) corresponding to that day (31 October 2016).

    You mention that “LastNonblank returns the last non blank value if finds in the column”. Actually I would say it “returns the first last non blank value it finds in the column” since the iteration is performed in the reverse order !

    • You are right Tristan. You are talking about an engine optimisation and it makes sense to start from the bottom up. However I never actually mention which order the iteration operates in my post, and I think to do so would just add complexity that doesn’t improve the reader’s understanding.

      I have added a small caveat early in the post for those that are interested.

  5. Hi Matt, I liked this post so much that I bought the book! I came here while looking for a way to pass a filtered date table to LASTNONBLANK.
    The setup:
    I need to measure open balances in my data, where previous months are represented by just one end-of-month entry, but the current month’s open balance is represented by an entry for every day of the month, even those that are after today. I am using LASTNONBLANK to get the last day of a month, so naturally this works well for every past month, but for the current month it fetches the end-of-month date.
    This is not a problem when I am only summarising my open balance, as any future date simply holds the current balance as at today, but it *is* a problem if I perform any date-based calculation (such as aging) on the data: if the LASTNONBLANK day is in the future (e.g. 31st March when today is the 7th March), and I am counting days between some past event and the current open-at-end date, this aging is off – I need to measure until the current day.
    So, given this scenario, I want to restrict LASTNONBLANK to work on a filtered data set where dates are no greater than today. Furthermore, I have other filter considerations as well, such as record type. To translate this to your example imagine an additional column in the Data table named AccountType, where Matt is “Domestic” and Kathy is “Foreign”, and that the dates in the data have been brought forward to this year, 2017, meaning some of it is past (until the end of February) and the rest is future dated. And finally, I need to use the related Calendar[Date] field, rather than the date embedded in my data table, as in my case dates are joined by an integer identifier in YYYMMDD format, rather than a date (I know not best practice here but inherited from a long-standing design).
    My first step is to return the correct last non-blank date no greater than today, once I have this I will use it to calculate my balance, and returning data that is not future dated.
    I can get the very last non-blank Domestic sale date like this, by using the Calendar date as my expression into LASTNONBLANK:

    Last Non Blank Current Domestic Balance Date:=LASTNONBLANK ( Calendar[Date],
    CALCULATE(
    COUNTROWS(Data),
    Data[AccountType] = “Domestic”,
    Data[Date] = TODAY(), therefore I cannot include that filter on Data[Date] in the CALCULATE, I think instead that I need to filter the data supplied to LASTNONBLANK. Can you show me the correct solution?

  6. Hi Matt

    I have worked this out by adding a calculated column called CalendarDate to my Data table, that consists of RELATED(Calendar[Date]). The point being that I need to emulate the scenario where my dates only exist in the Calendar table, and I have date IDs to link to Calendar.

    Last Non Blank Current Domestic Balance Date:=LASTNONBLANK ( Calendar[Date],
    CALCULATE(
    COUNTROWS(Data),
    Data[AccountType] = “Domestic”,
    Data[CalendarDate] <= TODAY())
    )

    Is there any better way to do this?

    Thanks

    • Hi Chris. Hope you find the book useful. It is always easier to explain a problem than it is to read and understand :-). I don’t fully understand however my intuition is that you could write 2 measures – one that works in each of the scenarios and then use an if statement to detect if you are in the current month or a previous month to return the correct result. Could that work? If I have misunderstood, I suggest you create a sample workbook containing the formulas and demonstrating the problem, and then post it at http://powerpivotforum.com.au

Leave a Reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x