Downloads
DAXTime intelligence: first and lastAdvanced

FIRSTNONBLANK

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

Syntax
FIRSTNONBLANK(<column>, <expression>)

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

What it does

FIRSTNONBLANK walks through the values of a column in order and stops at the first one where a measure gives a value. Used with dates, it finds the first day on which something happened, such as the first invoice to a customer or the first sale of a product. It is the right tool when the question is "when did activity first occur?", because FIRSTDATE only reads the calendar.

Syntax and arguments

Example

A sales manager wants the date of the first invoice for each customer. The Calendar is related to the invoice date, so the expression can count invoices on each day for the customer in context.

First Invoice Date =
MINX(
    FIRSTNONBLANK('Calendar'[Date], CALCULATE(COUNTROWS('Invoices'))),
    'Calendar'[Date]
)

FIRSTNONBLANK finds the earliest calendar day with at least one invoice for the customer. MINX reads that day back as a single date. For a customer with no invoices, the result is blank.

Example: a second, different use

The first day on which a product had stock movement in the year, for a slow-moving stock report. The expression is a sum of the movement, which is blank when nothing moved.

First Movement Date =
MINX(
    FIRSTNONBLANK('Calendar'[Date], SUM('Stock_Movements'[Quantity])),
    'Calendar'[Date]
)

Things to watch

In business terms

FIRSTNONBLANK supports the first invoice date for customer onboarding reports, the first sale of a new product, the start of a vendor relationship and the age of an account. These dates feed cohort analysis, customer lifetime and audit questions about when a relationship began.

Related functions

All DAX functions · View as Markdown · DAX fundamentals