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
——————————————————————————————————–
Be the first to comment