Downloads
DAXTime intelligence: first and lastAdvanced

LASTNONBLANK

Returns the last value in a column, in order, where the expression is not blank.

Syntax
LASTNONBLANK(<column>, <expression>)

Returns: A table with one value, used with an iterator such as MAXX or inside a filter.

What it does

LASTNONBLANK walks backward through the values of a column and stops at the last one where a measure gives a value. Used with dates, it finds the most recent day on which something happened, which is the last invoice, the last payment or the last stock count. It is also the standard way to read a closing balance when the snapshot does not have a row on the last day of the month.

Syntax and arguments

Example

A collections team wants the date of the most recent invoice for each customer, to spot accounts that have gone quiet.

Last Invoice Date =
MAXX(
    LASTNONBLANK('Calendar'[Date], CALCULATE(COUNTROWS('Invoices'))),
    'Calendar'[Date]
)

LASTNONBLANK finds the latest calendar day with at least one invoice for the customer in context. MAXX returns that day as a single date. A customer with no invoices in the filter returns a blank.

Example: a second, different use

The closing stock at the last count date in a month, when the stock count is taken on some days and not others. The balance is read on the last day that has a count.

Closing Stock (last count) =
CALCULATE(
    SUM('Inventory'[Units]),
    LASTNONBLANK('Calendar'[Date], CALCULATE(COUNTROWS('Inventory')))
)

Unlike CLOSINGBALANCEMONTH, this measure does not need a row on the last calendar day. It finds the last day that has counts within the filter context.

Things to watch

In business terms

LASTNONBLANK supports last invoice and last payment reports, dormant customer lists, the closing stock at the last count and the closing balance of a snapshot with gaps. Controllers use it to make sure the balance date used in a close is the last date with real evidence behind it.

Related functions

All DAX functions · View as Markdown · DAX fundamentals