# 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 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

## A Best Practice Implementation Strategy for Power BI

In the past, businesses were run based on what you know and who you know.  That is still true...

## Power BI Trend Analysis: Are Margins Expanding Or Contracting?

In this blog post, I will be diving into a relatively specific insight by conducting a Power BI trend...

## Data Visualization Report Frameworks | Part 2

For today, we'll be continuing the discussion on our enhanced visualization frameworks for Power BI....

## Create A Power BI Sparkline Chart In Report Builder

In this Power BI Report Builder tutorial, youâ€™ll learn how to add a sparkline chart in your paginated...

## Using Iterating Functions SUMX And AVERAGEX In Power BI

One of the most crucial topics for any Power BI beginner to know about is iterating functions. You may...

## PowerApps Documentation: Using MS Docs For Expert Functionalities

Let's talk about Microsoft's PowerApps documentation and what an important resource it can be for users...

## Data Modeling In Power BI: Tips & Best Practices

In this tutorial, you'll learn valuable tips and best practices for data modeling in Power BI. You can...

## Using The Query Builder Feature In DAX Studio

In this tutorial, youâ€™ll learn how to use the Query Builder feature to easily create queries and...

## Time-Related Insights From Your Supply Chain Metric

For this tutorial, I'm going to cover some high-quality time-related insights directly from your supply...

## Create A Multilingual Power BI Report

For today's blog, I want to discuss a not-unusual situation with many of my consulting assignments for...

## CROSSJOIN DAX Function: Server Timings & Query Plan

In this tutorial, youâ€™ll learn how the CROSSJOIN function works in DAX using the Server Timings pane...

## Overview Of The DAX Studio Keyword COLUMN

Another important keyword to learn when using DAX Studio is the COLUMN keyword. In simplest terms, the...