Using LASTNONBLANK In Your DAX Formula

The LASTNONBLANK function is an interesting DAX formula that you can utilize in a number of different ways inside of Power BI. It is not a very used DAX formula because of the complexities around how it calculates results.

No comments

The LASTNONBLANK function is an interesting DAX formula that you can utilize in a number of different ways inside Power BI. You may watch the full video of this tutorial at the bottom of this blog.

However, this DAX formula is not frequently used because of the complexities around how it calculates results.

I would presume most Power BI users don’t even realize that they can utilize the function in a variety of ways inside Power BI.

The idea in this tutorial is to demystify some of the techniques that can be utilized for this particular function.

In this blog post, I will show some examples of LASTNONBLANK. I will show the way I’ve used this function in this report, which you can view on the Enterprise DNA resources page.

I wanted to see the Last Sale Date and then calculate the Last Sale Amount in a dynamic way. This way, we can click through any part of our report and really drill into any aspect.

For this particular report, I wanted to analyze when the last time we sold to certain customers was. Since the report is dynamic, it will update for us then and there based on the current context.

Obviously, you have to understand all the context that you have on a particular result. But if you wanted to highlight when a last sale was, you can do so using a one-off card like I have in this example.

This card showcases when was the last sale at any point in time, based on your current selection.

You can utilize this technique in different ways and use this across different dimensions in your model.

Calculating The Last Sale Date

In this particular case, we wanted to have a table of information which shows us the Last Sale Date for every product we sell (left table).

But we also wanted to see the Last Sale Date for every single customer who bought from us (right table). This way, we can analyze how long it has been since a customer bought from us.

The LASTNONBLANK function enables us to do these kinds of analysis. Let’s have a look at the formula I used to come up with these two tables.

DAX formula

If you think about it, this is a relatively simple formula. We put the LASTNONBLANK inside a table or column.

In this case, we put it on a Date column and then evaluated when was the last sale that occurred for that particular element in our data.

By doing this, we can analyze and showcase the Last Sale Date.

Calculating The Last Sale Amount

But what’s also great is we can utilize this measure to work out a new measure, which we’ll call the Last Sale Amount. To begin, I dragged these two Key Measures in my table:

DAX formula

Then I used this really effective formula where we utilized the LASTNONBLANK as a filter.

DAX formula

Instead of calculating the date result, we calculated the sales results. And again, this is going to be dynamic based on the selection that we make.

The LASTNONBLANK is quite a versatile function because not only can you use it to retrieve a result, you can also use it to filter results.

Using LASTNONBLANK As A Scalar Function

What LASTNONBLANK actually does is to return a table. The table, in most cases, is just going to be the one result.

Our table will return a singular value because it is a scalar valued function. Scalar means to return a single value like a number, text, date, etc.

While LASTNONBLANK is a table function and as such, will always return a table, it will only return one value.

So in this case, it’s going to filter only one value, which is the last date. Then this filter will enable us to extract the Total Sales on that last date.

Conclusion

In this tutorial, I have shown you a couple of examples of how to utilize LASTNONBLANK. I have also shown you some of the logic that goes into the function.

Because of its versatility, you can find insights which historically have been very difficult to find. You can find them with quite a simple formula syntax with this function.

If you think about how difficult these sort of insights are when using tools like Excel, you’ll be amazed how intuitive and effective this function is to find insights that refer to the last of something or the first of something.

For more DAX formula deep dives, check out the below course at Enterprise DNA Online. If you really want to master DAX, then is the course to check out first.

Mastering DAX Calculations

Sam

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events

membership banner 3

***** Related Links *****
Last Purchase Date in Power BI: When Did Your Customers Make Their Last Purchase?
Show Results Up To Current Date Or A Specific Date In Power BI
Show Last N Sales Of A Customer Only Using Power BI

***** Related Course Modules *****
Ultimate Beginners Guide to DAX
Mastering DAX Calculations
DAX Formula Deep Dives

***** Related Support Forum Posts *****
Last Sale Amount – LASTNONBLANK
LASTNONBLANK, Unfiltered
LASTDATE With Condition
For more LASTNONBLANK support queries to review see here….

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.