How to Use EARLIER in Power BI

Working with DAX in Power BI can feel like learning a new language, and functions like EARLIER often trip up even experienced users. It's a powerful tool, but its purpose isn't immediately obvious. This guide will break down exactly what the EARLIER function does, why it exists, and how you can use it to perform valuable calculations like running totals and rankings in your reports.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is the EARLIER Function in DAX?

In simple terms, the EARLIER function gives you access to a value from a previous “row context” in your calculation. It sounds abstract, but think of it as a way to look one step "outside" the current calculation your formula is running. DAX often works by iterating through a table row by row, and EARLIER lets you "remember" the value of the row that started the process while you are working on another row inside that process.

This is most common in calculated columns or within iterating functions like FILTER, SUMX, or COUNTROWS. You have a main, or "outer," row context and then a second, "inner," row context created by the iteration. EARLIER reaches out from the inner context to grab a value from the outer one.

The formal syntax for the function is:

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

• <column>: This is the column from the "outer" row context that you want to retrieve a value from.
• [,<,number>:] (Optional): This tells DAX how many layers "out" to look. If you omit it, it defaults to 1, meaning it looks at the very next outer context. Using EARLIER(<column>, 2) would have it skip one level and go two levels out. In practice, you will almost always use the default.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The Key to Understanding EARLIER: Row Context

Before any of the practical examples make sense, you must understand a core concept in DAX: row context. A row context is simply DAX’s awareness of the "current row" it is working on.

When you create a simple calculated column, you are using a basic row context. For example, if you have a sales table and create this column:

Total Revenue = Sales[Quantity] * Sales[Unit Price]

For each row in the Sales table, DAX understands the "current row" and correctly multiplies the Quantity and Unit Price from that specific row. That's a single, simple row context.

Nested row contexts happen when you initiate another scan of a table while you are already in a row context. This is where things get interesting. Imagine for each row in your main calculation, your formula needs to scan the entire table again to find some related information. Now you have two row contexts:

1. The Outer Context: The original row your calculated column is evaluating.
2. The Inner Context: The specific row being scanned inside an iterating function like FILTER.

Without EARLIER, the inner context has no idea what the outer context is doing. EARLIER solves this problem by allowing the inner context to reference a value from the outer context.

How to Use EARLIER: Practical Examples

Theory is great, but let's see how this works in real-world scenarios. The two most common and classic uses for EARLIER are calculating running totals and creating rankings.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Example 1: Calculating a Running Total in a Column

A running total (or cumulative sum) is a calculation that shows how a value adds up over time. It’s a very common business request. Let's say you have a simple Sales table with dates and sales amounts, and you want to add a column that shows the cumulative sales up to that date.

Our Sample Data (Sales Table)

Imagine your table looks like this:

• OrderDate
• Product
• SalesAmount

The Goal

We want to add a new calculated column called RunningTotal. For any given row, this column's value should be the sum of all SalesAmount values where the OrderDate is less than or equal to the OrderDate of the current row.

The DAX Formula

To achieve this, you would create a new calculated column with this formula:

RunningTotal = CALCULATE ( SUM ( Sales[SalesAmount] ), FILTER ( ALL ( Sales ), Sales[OrderDate] <= EARLIER ( Sales[OrderDate] ) ) )

Breaking It Down Step-by-Step:

1. Calculated Column Context: Power BI starts by going through the Sales table row by row to fill in the RunningTotal column. Let's focus on one row, say the one for January 3rd, where SalesAmount is $200. This is our outer row context.
2. CALCULATE(SUM(Sales[SalesAmount]), ...): We tell DAX we want to calculate the sum of sales, but only for the rows that meet the criteria in the filter.
3. FILTER(ALL(Sales), ...): This starts its own iteration - a new scan of the entire Sales table (using ALL(Sales) to ignore any filters). For each row checked, it creates a new, temporary, inner row context.
4. Sales[OrderDate] <= EARLIER(Sales[OrderDate]): This is key.

So, for our January 3rd row, the formula finds all rows with OrderDate before or on January 3rd and sums their SalesAmount. This process repeats for all rows, creating a running total.

Example 2: Creating a Sales Rank

Another common use for EARLIER is ranking. Suppose you want to rank each transaction from highest SalesAmount to lowest.

The Goal

Create a SalesRank column that assigns rank 1 to the highest sale, 2 to the second-highest, etc.

The DAX Formula

SalesRank = COUNTROWS ( FILTER ( Sales, Sales[SalesAmount] > EARLIER ( Sales[SalesAmount] ) ) ) + 1

Breaking It Down Step-by-Step:

1. Outer Context: The formula processes each row in Sales. For a row where the SalesAmount is, say, $500.
2. COUNTROWS(FILTER(...)): Counts how many rows satisfy the condition inside.
3. FILTER(Sales, Sales[SalesAmount] > EARLIER(Sales[SalesAmount])): Checks all rows in Sales:

It counts how many sales are greater than the current sale. Adding 1 assigns the rank.

Things to Watch Out For & Modern Alternatives

While powerful, EARLIER is one of the more veteran functions in DAX, and there are some things to keep in mind.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

1. Performance on Large Datasets

Because EARLIER's logic involves nested iterations (scanning a table inside a scan), it can be slow on very large tables with millions of rows. The engine has to perform a lot of work for each row. If your report refreshes slowly, a calculation using EARLIER could be the culprit.

2. The Modern Alternative: Using Variables (VAR)

In many cases, modern DAX allows you to achieve the same result as EARLIER with variables, which are often easier to read and perform better. A variable allows you to "store" a value from the outer row before your inner calculation.

Let's rewrite the running total example using a variable:

RunningTotal with VAR = VAR CurrentRowDate = Sales[OrderDate] RETURN CALCULATE ( SUM ( Sales[SalesAmount] ), FILTER ( ALL ( Sales ), Sales[OrderDate] <= CurrentRowDate ) )

Here, CurrentRowDate captures the date from the outer context and is used inside the FILTER. Many DAX practitioners prefer this method for clarity and performance. Nonetheless, understanding EARLIER is still essential for reading older code and grasping context flow.

Final Thoughts

The EARLIER function can be challenging at first, but it unlocks a new level of calculation in Power BI. By understanding how it navigates between nested row contexts, you can produce sophisticated calculations like running totals and rankings, enhancing your reports' analytical power.

Mastering these concepts takes time, but it pays off by enabling more dynamic and insightful data visualizations. And with modern AI-powered tools like Graphed, managing the complexity becomes easier—just ask for your desired analysis, and the tool builds it for you, connecting to your data sources and providing instant dashboards.