EARLIER
Returns the value of a column from an outer row, while a formula loops over an inner row.
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
- column: the column whose value on the outer row you want.
- number (optional): how many row contexts to go back. The default is one level.
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
- EARLIER works in calculated columns and inside iterators that have a row context. It does not work in a measure, which has no outer row.
- EARLIER returns the value one level out by default. Use
EARLIESTfor the outermost row when nesting goes deeper. - Running totals with EARLIER slow down as the table grows. A measure is faster.
- Calculated columns are stored in the model and take memory. Large ones add to the file size.
- Prefer variables inside measures. They are easier to read and test.
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.