EARLIER
A column's value in an outer row context: the one before, or a number of levels out.
Group: Filter. Compatible with DAX.
Syntax
EARLIER ( <column> [, <number>] )| Parameter | Type | Description |
|---|---|---|
column | Column | The column whose value in an outer row context is wanted. |
number | Number optional | How many row contexts to go out; 1, the one just outside, when left out. |
Returns
Same type as the column. The column's value in that outer row.
Remarks
- It is needed when two iterations go over the same table, so that the column's name alone means the inner row:
FILTER ( Products, Products[Price per liter] > EARLIER ( Products[Price per liter] ) )compares every product with the outer row's. - Without an outer row context that has the column it is an error: in a measure on its own, or with only one iteration.
- With a number it goes out that many row contexts:
EARLIER ( column, 2 )reads the row two iterations out. - A variable usually reads better: store the outer row's value with VAR before the inner iteration starts, and compare with the variable. The second example shows both giving the same.
Examples
A price rank and a count per category
EVALUATE
ADDCOLUMNS (
Products,
"Price rank", COUNTROWS ( FILTER ( Products, Products[Price per liter] > EARLIER ( Products[Price per liter] ) ) ) + 1,
"Same category", COUNTROWS ( FILTER ( Products, Products[Category] = EARLIER ( Products[Category] ) ) )
)
ORDER BY Products[Price per liter] DESC| Products[Product] | Products[Category] | Products[Price per liter] | Price rank | Same category |
|---|---|---|---|---|
| IPA | Beer | 64 | 1 | 3 |
| Cider | Cider | 52 | 2 | 1 |
| Pils | Beer | 38 | 3 | 3 |
| Lettøl | Beer | 30 | 4 | 3 |
The same rank with a variable
EVALUATE
ADDCOLUMNS (
Products,
"With EARLIER", COUNTROWS ( FILTER ( Products, Products[Price per liter] > EARLIER ( Products[Price per liter] ) ) ) + 1,
"With a variable",
VAR Price = Products[Price per liter]
RETURN COUNTROWS ( FILTER ( Products, Products[Price per liter] > Price ) ) + 1
)
ORDER BY Products[Price per liter] DESC| Products[Product] | Products[Category] | Products[Price per liter] | With EARLIER | With a variable |
|---|---|---|---|---|
| IPA | Beer | 64 | 1 | 1 |
| Cider | Cider | 52 | 2 | 2 |
| Pils | Beer | 38 | 3 | 3 |
| Lettøl | Beer | 30 | 4 | 4 |
Related functions
EARLIEST, FILTER, ADDCOLUMNS, RANKX, SUMX.