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.
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:
Then I used this really effective formula where we utilized the LASTNONBLANK as a filter.
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.
***** 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
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.
Sam
