LASTNONBLANK & FIRSTNONBLANK - How To Use This Two DAX Function In Power BI

[EnterpriseDNA]

LASTNONBLANK and FIRSTNONBLANK are interesting DAX functions that you can use in many ways in Power BI. 

However, these functions are not frequently used because of the complexities around how they calculate results. I would presume most Power BI users don’t even realize that they can utilize these functions in a variety of ways inside Power BI. 

These two functions are pretty much the same. They both return the value in a column that is filtered by the current context, where the expression is not blank. The only difference between them is that LASTNONBLANK returns the last value while FIRSTNONBLANK returns the first value. 

The idea in this blog post is to clarify some of the techniques being utilized with these functions. Here I’ll share a couple of my tutorials about the LASTNONBLANK function. You’ll see how I’ve used this function in a report. Since FIRSTNONBLANK and LASTNONBLANK are quite alike, you’ll learn how to use FIRSTNONBLANK through these tutorials as well. 

In this first tutorial, I wanted to know what the last sale date was so I could calculate the last sale amount. I wanted it to be dynamic as well, so that I could also click through any part of my report and really drill into any aspect.

LASTNONBLANK is a scalar-valued function that returns a table. This means that it returns a single value such as a number, text, date, etc. So, in this case, it is filtering only one value, which is the last date. 

Watch the video tutorial below to see how I used LASTNONBLANK in this analysis andto achieve my desired results. 

There are other ways to calculate for the last or first of something, but this is just an example of how you can achieve it by using LASTNONBLANK. 

The second tutorial that I want to share with you is a report that involves handling multiple currencies.

Initially I used the LOOKUPVALUE function for this analysis.

However, I found an issue in the results. The report was not showing the exchange rates in the weekends, even though there are goods sold on the weekends. 

To solve this, I used the LASTNONBLANK function. Watch the video below and check out the formula that I created, which extends the Friday number over Saturday and Sunday. 

This is a very unique way of using LASTNONBLANK in a measure. In the same way, the insights that you get from this are quite amazing as well. If you could understand how this function works, it will be easy for you to utilize it in your own reports and analyses. 

Key Take Away 

In this blog post, I’ve shown you a couple of my tutorials on how to use LASTNONBLANK. I’ve also shown you some of the logic and other DAX functions that go with this function. 

LASTNONBLANK, which is very similar to FIRSTNONBLANK, is quite versatile. You can find insights that have been very difficult to find before Power BI and DAX were around. You can extract unique insights with simple formula syntax using these two functions. 

Think about how difficult it is to get these insights using tools like Excel. With Power BI, you’ll be amazed how intuitive and effective these functions are to get insights that refer to the last or first of something. 

Keep on learning Power BI DAX functions and check out the related links below. 

All the best! 

Sam 

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

Mastering DAX Calculations 

DAX Formula Deep Dives 

Advanced DAX Combinations 

***** Related Support Forum Posts ***** 

Last Sale Amount – LASTNONBLANK 

LASTNONBLANK, Unfiltered 

LASTDATE With Condition 

For more LASTNONBLANK support queries to review see here….