Home » DAX » DAX LASTNONBLANK Function

DAX LASTNONBLANK Function

LASTNONBLANK

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

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

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

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.

Leave a Reply