Sunday, February 3, 2019

How to use FIRSTNONBLANK and LASTNONBLANK Functions in Power BI DAX

How to extract FIRST NON BLANK and LAST NON BLANK values from a Column in Power BI
The FIRSTNONBLANK() Function returns the first value in the column, filtered by the current context, where the expression is not blank.
Syntax :
FIRSTNONBLANK(<Column>,<Expression>)

The LASTNONBLANK() Function returns the last value in the column, filtered by the current context, where the expression is not blank.
Syntax :
LASTNONBLANK(<Column>,<Expression>)  

Parameters :
Column - A column expression.
Expression - An expression evaluated for blanks for each value of column.

Scenario :
Suppose we have Power BI Model as follows..

Now we can calculate and extract the First Order Date and the First Order Value in Fiscal Year 2015, using the DAX Function "FIRSTNONBLANK()" as follows..

First OrderDate(2015) = CALCULATE(FIRSTNONBLANK(tbl_OrderDetails[Order_Date],TRUE()),
                                                 FILTER( ALL(tbl_Calendar),tbl_Calendar[Year]=2015))
                                                
First OrderValue(2015) = CALCULATE([Net Sales],
FILTER(tbl_Calendar,tbl_Calendar[cDate]=CALCULATE(FIRSTNONBLANK(tbl_OrderDetails[Order_Date],TRUE()),
FILTER( ALL(tbl_Calendar),tbl_Calendar[Year]=2015))))

We can see the result of the above Functions as follows..


Now we can calculate and extract the Last Order Date and the Last Order Value in Fiscal Year 2015, using the DAX Function "LASTNONBLANK()" as follows..

Last OrderDate(2015) = CALCULATE(LASTNONBLANK(tbl_OrderDetails[Order_Date],TRUE()),
                                                 FILTER( ALL(tbl_Calendar),tbl_Calendar[Year]=2015))

Last OrderValue(2015) = CALCULATE([Net Sales],
FILTER(tbl_Calendar,tbl_Calendar[cDate]=CALCULATE(LASTNONBLANK(tbl_OrderDetails[Order_Date],TRUE()),
FILTER( ALL(tbl_Calendar),tbl_Calendar[Year]=2015))))

We can see the result of the above Functions is as follows..

-------------------------------------------------------------------------------------------------------- 
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.