Using FirstNonBlank / LastNonBlank in DAX

As per Microsoft’s Developer Network library,

FirstNonBlank /LastNonBlank return the first/last value respectively in the column, column, filtered by the current context, where the expression is not blank.

https://msdn.microsoft.com/en-us/library/ee634210.aspx

https://msdn.microsoft.com/en-us/library/ee634247.aspx

However this is half the truth….

FirstNonBlank /LastNonBlank return the first/last value respectively in the column…..after sorting the column in its native Ascending Order….column, filtered by the current context, where the expression is not blank.

So lets take a simple one column table of 12 month to test this.

Months

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Copy this data to your Excel workbook or Power BI desktop and add this Calculated Column

Column =
FIRSTNONBLANK ( ALL ( TableName[Months] ), 1 )

You will find “April” as the result…and not the “January” first month of your data

This understanding is very important to get correct results.

Following example will make it very clear why this is important.

Following is a simple Table of 2 columns. 3 Column is the result (calculated column) we want to get

In short we want to fill the blank values with the lastnonblank values before that date

Date	Value	Desired Values
01-Jan-17	500	500
02-Jan-17	200	200
03-Jan-17		200
04-Jan-17	1000	1000
05-Jan-17		1000
06-Jan-17		1000
07-Jan-17	300	300
08-Jan-17	400	400
09-Jan-17		400
10-Jan-17		400

The natural approach would be to use this formula

Column =
 IF (
 ISBLANK ( TableName[Value] ),
 CALCULATE (
 LASTNONBLANK ( TableName[Value], 1 ),
 FILTER ( ALL ( TableName ), TableName[Date] <= EARLIER ( TableName[Date] ) )
 ),
 TableName[Value]
 )

Above formula would have yielded correct results if the LastNonBlank function didnot sort the column TableName[Value]… Following are the results of this formula

 

 

 

 

 

What went wrong?

To understand this lets take the third row in above example i.e. Date 3 Jan 2017 where we expected a VALUE of 200 instead of 500

 

 

The following part of the formula filters the Table and returns the first 3 rows only

FILTER ( ALL ( TableName ), TableName[Date] <= EARLIER ( TableName[Date] ) ) )

i.e.

 

 

Then following part of the formula

LASTNONBLANK ( TableName[Value], 1 )

sorts the filtered Column “TableName[Value]” resulting from applying the FILTER function

i.e.

Therefor instead of 200…….. 500 is the result of applying LASTNONBLANK

THE CORRECT WAY then is to compute the LASTNONBLANK date first and then to use it to determine the correct LASTNONBLANK value.

Following formula will do the job

New Value =
VAR LastNonBlankDate =
 CALCULATE (
 LASTNONBLANK ( TableName[Date], 1 ),
 FILTER (
 ALL ( TableName ),
 TableName[Date] <= EARLIER ( TableName[Date] )
 && NOT ( ISBLANK ( TableName[Value] ) )
 )
 )
RETURN
 CALCULATE (
 SUM ( TableName[Value] ),
 FILTER ( ALL ( TableName ), TableName[Date] = LastNonBlankDate )
 )