Downloads
DAXFilter and contextAdvanced

EARLIER

Returns the value of a column from an outer row, while a formula loops over an inner row.

Syntax
EARLIER(<column> [, <number>])

Returns: A scalar value from the outer row.

What it does

EARLIER lets a formula that loops over a table (with FILTER or an iterator) refer back to the row that started the loop. The classic use is a running total in a calculated column: for each sales line, add up all lines dated on or before this one. Newer models usually do this with a measure and a date filter, or with a variable. Reach for EARLIER when you are inside a calculated column and need the outer row.

Syntax and arguments

Example

A running total of sales by date, as a calculated column on the Sales table.

Running Sales =
CALCULATE (
    SUM ( Sales[SalesAmount] ),
    FILTER (
        ALL ( Sales ),
        Sales[DateKey] <= EARLIER ( Sales[DateKey] )
    )
)

For each sales line, the FILTER keeps every line dated on or before the current line's date. The sum is the running total up to that date. Lines on the same date get the same running total, because they include the same set of lines.

This formula visits the whole table for every row, so it is slow on large tables. A measure with a date filter is much faster for reports.

Example: a second use

A calculated column that counts the sales lines in the same store with a smaller amount than this line.

Lines Below This Amount =
COUNTROWS (
    FILTER (
        Sales,
        Sales[StoreKey] = EARLIER ( Sales[StoreKey] )
            && Sales[SalesAmount] < EARLIER ( Sales[SalesAmount] )
    )
)

The FILTER loops over Sales, and EARLIER returns the store and amount of the line being calculated. The result is a rank-style count for each line within its store.

Things to watch

In business terms

Cumulative sales for a Pareto or ABC analysis, running balances in a ledger extract, and rank-within-group counts. Many finance teams build these in Power Query or SQL instead, because the steps are easier to audit there.

Related functions

All DAX functions · View as Markdown · DAX fundamentals