EARLIER function is hard to understand in first go but it is even harder to explain.
“EARLIER” is needed when you need to compare each Row of a Table against every other Row of the same table.
It means that this function
- Requires atleast two row contexts
- Will mostly be used in CALCULATED columns because row contexts typically exists there
Lets understand it with a very simple example
Following is a simple table showing top 10 Richest person on the planet and their net worth in 2017. We will load this data into Power Pivot. Click here to Download Excel file
| Name | Net worth (USD Billion) | Age | Nationality | Source(s) of wealth |
| Amancio Ortega | 71.3 | 80 | Spain | Inditex, Zara |
| Bill Gates | 86.0 | 61 | United States | Microsoft |
| Carlos Slim | 54.5 | 77 | Mexico | América Móvil, Grupo Carso |
| Charles Koch | 48.3 | 81 | United States | Koch Industries |
| David Koch | 48.5 | 76 | United States | Koch Industries |
| Jeff Bezos | 72.8 | 53 | United States | Amazon.com |
| Larry Ellison | 52.2 | 72 | United States | Oracle Corporation |
| Mark Zuckerberg | 56.0 | 32 | United States | |
| Michael Bloomberg | 47.5 | 75 | United States | Bloomberg L.P. |
| Warren Buffett | 75.6 | 86 | United States | Berkshire Hathaway |
OUR OBJECTIVE is to add a CALCULATED COLUMN that would give us the RANK these billionaires. One way of doing this is to compare each row against every other row of the table. For example if Bill Gates net worth is compared row by row against other billionaires, we can then count the number of rows where Bill Gates Net worth is lower than or equal to others. This will give us his rank.
This means doing something like this
- Bill Gates =< Amancio Ortega
- Bill Gates=< Bill Gates
- Bill Gates=< Carlos Slim
- Bill Gates=< Charles Koch
- Bill Gates=< David Koch
- Bill Gates=< Jeff Bezos
- Bill Gates=< Larry Ellison
- Bill Gates=<Mark Zuckerberg
- Bill Gates=<Michael Bloomberg
- Bill Gates=< Warren Buffett
So Bill Gates Rank is 1 (Only 1 CASE is True above).
Now the same procedure is repeated against Next Billionaire. Lets take Amancio Ortega
- Amancio Ortega =< Amancio Ortega
- Amancio Ortega=< Bill Gates
- Amancio Ortega=< Carlos Slim
- Amancio Ortega=< Charles Koch
- Amancio Ortega=< David Koch
- Amancio Ortega=< Jeff Bezos
- Amancio Ortegas=< Larry Ellison
- Amancio Ortega=<Mark Zuckerberg
- Amancio Ortega=<Michael Bloomberg
- Amancio Ortega=< Warren Buffett
So Amancio Ortega’s Rank is 4 (4 CASES found True above).
And so on this process is repeated for each billionaire.
SO HOW EARLIER HELPS US DO THIS
We know that a Calculated Column already creates a Row Context. Now we need another Row Context on the same Table. This Row Context is Provided by ITERATOR function like FILTER, SUMX etc
Remember this behavior of ITERATORS.
- ITERATOR functions always create a NEW ROW CONTEXT on the Table on which ITERATOR is applied. You can consider this New Row Context as a temporary TABLE which exists in the memory
- ITERATORS hide the PREVIOUS ROW CONTEXTs created on the SAME TABLE
- These hidden ROW CONTEXTS can be called using the “EARLIER function”
THE DAX FORMULA that will do this is
This is how this formula worked
1)FILTER created a NEW ROW CONTEXT on TABLE1
2) THE EXISTING row context coming from the CALCULATED COLUMN got hidden which is recalled using EARLIER
3) FILTER is an ITERATOR, so it ITERATES through TABLE1 (first parameter) and evaluates the second parameter i.e. (EARLIER(Table1[Net worth (USD Billion)])<=Table1[Net worth (USD Billion)]) for each row of TABLE1
| HIDDEN ROW CONTEXT | FILTER’s ROW CONTEXT | |
| Bill Gates | =< | Bill Gates |
| Bill Gates | =< | Warren Buffett |
| Bill Gates | =< | Jeff Bezos |
| Bill Gates | =< | Amancio Ortega |
| Bill Gates | =< | Mark Zuckerberg |
| Bill Gates | =< | Carlos Slim |
| Bill Gates | =< | Larry Ellison |
| Bill Gates | =< | Charles Koch |
| Bill Gates | =< | David Koch |
| Bill Gates | =< | Michael Bloomberg |
| Warren Buffett | =< | Bill Gates |
| Warren Buffett | =< | Warren Buffett |
| Warren Buffett | =< | Jeff Bezos |
| Warren Buffett | =< | Amancio Ortega |
| Warren Buffett | =< | Mark Zuckerberg |
| Warren Buffett | =< | Carlos Slim |
| Warren Buffett | =< | Larry Ellison |
| Warren Buffett | =< | Charles Koch |
| Warren Buffett | =< | David Koch |
| Warren Buffett | =< | Michael Bloomberg |
| And So On |
USING VARIABLES INSTEAD OF EARLIER
If you have Excel 2016 you can use DAX variables instead of EARLIER function.
DAX variables are used to store a value which can then be used several times in the FORMULA. The formula below will do the same job as the one that uses EARLIER
= VAR CurrentRow = Table1[Net worth (USD Billion)]
RETURN
Countrows(Filter(Table1,CurrentRow<=Table1[Net worth (USD Billion)]))
Please leave your feedback below