The LASTNONBLANK function returns the last value in a column for which an expression does not return a Blank.
LASTNONBLANK(
column,
expression
)
- column: Column whose values you want to analyze.
- expression: Expression evaluated for each value in the column.
The LASTNONBLANK function returns a table containing a single column with a single value corresponding to the last value of the column for which the indicated expression does not return a Blank.
The column argument can be a reference to an existing column, a table with a single column, or a Boolean expression that defines a table with a single column. In the latter case, the expression cannot refer to a measure, nor use the CALCULATE function nested, nor use iterative (table scan) functions or functions that return a table.
The indicated column is not added to the filter context for expression evaluation. That is, the expression is not going to be evaluated in the filter context created by each value in the column. If we want the context to be propagated, we must do so explicitly by calculating the expression as an argument to CALCULATE.
Before evaluating the expression, the table is sorted based on the values in the specified column.
To see how the LASTNONBLANK and LASTNONBLANKVALUE functions work, we start from the following table:
We can see how the last two columns include empty cells. We read the table from Power BI and create two measures applying both functions using the product of width ("Ancho" column) and height ("Alto" column) as an expression:
LASTNONBLANK =
LASTNONBLANK(
data[Valor],
CALCULATE(SUM(data[Ancho]) * SUM(data[Alto]))
)
In the previous expression we enclose the expression in the CALCULATE function so that it is evaluated in the row context created by each value of the indicated column.
LASTNONBLANKVALUE =
LASTNONBLANKVALUE(
data[Valor],
SUM(data[Ancho]) * SUM(data[Alto])
)
In this second expression it is not necessary to use the CALCULATE function since the LASTNONBLANKVALUE function already forces the expression included as the second argument to be calculated in the row context of each value in the column.
Now, we bring both measurements to a multi-row card:
We clearly see the differences between both functions: LASTNONBLANK returns values from the indicated column. Specifically, it returns the last value for which the expression does not return a Blank value (the 5 corresponding to the penultimate row). It is important to insist that, for the calculation of the function, the table is going to be ordered according to the values of the column. In this example the rows are already ordered according to this criterion, but the result obtained would be different if the order were different.
In contrast, LASTNONBLANKVALUE returns the result of the expression. Specifically, the last non-blank value obtained after going through the table ordered according to the values of the column.
