During a PowerPivot Workshop course we received an interesting question from a student: “Can I use LASTNONBLANK (and FIRSTNONBLANK) with a column which is not a date column?”
The reason is that we introduce LASTNONBLANK in the Advanced Time Intelligence module, because its typical use case is on a date column. However, you can use these functions on any column, which raises the question about what happens at that point. The sort order used is the one that depends on the data type of the column. If it is a Text column, the alphabetical sort order is the reference order. If it is a number, then the numeric order is the reference.
What happens if a column has the “Sort By Column” property set to another column? This sort order is *not considered* by LASTNONBLANK and FIRSTNONBLANK functions. Even if a PivotTable shows you data sorted according to Sort by Column property, any DAX formula ignores such a sort order. Thus, be careful writing your DAX queries if you have to do some assumptions on the sort order of a column using DAX functions that rely on sort order, such as LASTNONBLANK and FIRSTNONBLANK.
Returns the last value in the column for which the expression has a non blank value.
LASTNONBLANK ( <ColumnName>, <Expression> )
Returns the first value in the column for which the expression has a non blank value.
FIRSTNONBLANK ( <ColumnName>, <Expression> )