The LASTNONBLANK DAX function is used to returns the last value in the column for which the expression has a non blank value.
This function comes under Time Intelligence DAX functions category.
Syntax
LASTNONBLANK(<column>,<expression>)
Parameters
column- A column expression.
expression- An expression evaluated for blanks for each value of column.
Let’s get started
Import below dataset into Power BI desktop-
Table – Sales
| Date | Sales | Month | Year |
| 1/12/2017 | Dec | 2017 | |
| 5/1/2018 | 14000 | Jan | 2018 |
| 1/2/2018 | 12000 | Feb | 2018 |
| 6/2/2018 | 13000 | Feb | 2018 |
| 1/3/2020 | Mar | 2020 | |
| 6/3/2020 | Mar | 2020 |
Following these steps in order to implement Lastnonblank-
Step-1: Add one measure for Total Sales.
Total Sales = SUM(Sales[Sales])
Step-2: Add another measure and write below DAX for Lastnonblank.
LNB Date wise = LASTNONBLANK(Sales[Date], [Total Sales])
Step-3: Now add one card visual into Power BI report page and drag measure over it to see the result.
Lastnonblank DAX function
As you can see in above screenshot last non blank date according to Sales is 06/02/18.
Step-4: Now get last non blank for Month column, create one measure with below DAX.
LNB Month Wise = LASTNONBLANK(Sales[Month], [Total Sales])
Lastnonblank dax month
As you can see in above screenshot Table data last non blank month is Feb but DAX measure returns Jan.
Understand the reason behind this-
Power BI DAX engine sort the column data in backend then returns the value. To check this – sort the table data month column wise in ascending order.
After sort you will see last non blank month is Jan, because DAX engine sort the month column data alphabetically and “J” comes in last.
Lastnonblank DAX Power BI
Similar DAX functions: FIRSTNONBLANK
Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.