Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# Using LASTNONBLANK In Your DAX Formula

by | 9:00 am EDT | March 18, 2020 | DAX, Power BI

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.

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.

Mastering DAX Calculations

Sam

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## Funny ChatGPT Prompts: 20 Hilarious ChatGPT Ideas

In a world where technology continues to amaze us, we have now arrived at the point where we can have a...

## Power BI Slicer Search: User Guide With Examples

Ready to get started with the Power BI slicer? This feature will allow you to filter and slice your...

## SUMPRODUCT Multiple Criteria: Explained With Examples

Most Excel users think that the SUMPRODUCT function in Excel helps only to multiply the numbers in...

## Data Analytics Outsourcing: Pros and Cons Explained

In today's data-driven world, businesses are constantly swimming in a sea of information, seeking the...

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

## Power BI Waterfall Chart: A Detailed User Guide

In the world of data visualization, charts speak louder than numbers. If you're looking for a way to...

## Power BI Import vs Direct Query: Which is Better & Why?

In the world of data analysis, Power BI offers you a range of tools to connect to your data sources....

## Power BI Certification: Everything You Need to Know

In today's data-driven world, the ability to transform raw numbers into meaningful insights is more...

## Power BI Bookmarks: The Ultimate Guide

When working with data, bookmarks offer a streamlined and personalized way to navigate through large...

## Power BI Default Slicer Value Explained

One of the key features of Power BI is the slicer, which allows you to filter your data based on...