Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

What is the EARLIER Function in Power BI?

Cody Schneider••8 min read

Struggling to calculate a running total or create product rankings in Power BI? The DAX EARLIER function is designed for exactly these kinds of tasks, helping you compare values across different rows of your data. This article will break down what EARLIER actually does, show you how to use it with practical, step-by-step examples, and even cover the modern alternative you should probably be using instead.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Beyond the Definition: What Does EARLIER Actually Do?

Before diving into formulas, you need to understand a core concept in DAX: row context. When you create a calculated column, DAX evaluates your formula one row at a time. It can only "see" the data in the current row. For example, if you're on a row for a sale made on January 15th, the row context for that specific calculation only contains the information for January 15th.

But what if, for that same calculation on the January 15th row, you need to know the Total Sales from January 14th? This is where simple formulas break down. How can you access a value from a different row while you're still stuck in the context of the current row?

This is the problem EARLIER solves. It gives you a way to "step outside" of the current, innermost row context to grab a value from a previous, "earlier" stage of a calculation. Think of it like putting a bookmark on a page in a book. You can flip ahead a few pages, but the bookmark lets you reference the value you saved on that earlier page.

In DAX, EARLIER creates that bookmark. It holds onto the value from the main row you're crunching numbers for, so you can use it inside a nested function (like a FILTER) that's looping through the table all over again.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Deconstructing the EARLIER Syntax

The syntax for EARLIER seems simple on the surface, but its power is in how it interacts with other functions.

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

  • <column>: This is the column whose value you want to capture and use from that "earlier" row context.
  • [<number>] (Optional): This specifies how many levels of row context you want to step back. If you leave it blank, it defaults to 1, meaning it will grab the value from the next outer context. In most real-world scenarios, you will almost never need to use 2 or higher, as those apply to extremely complex, nested calculations. For our purposes, we'll focus on the default behavior.

Practical Examples: Putting EARLIER to Work

The best way to understand EARLIER is to see it in action. Here are a couple of classic use cases that demonstrate how it works in a calculated column.

Example 1: Calculating a Running Total

A running total, or cumulative sum, is one of the most common applications for EARLIER. Let's say you have a simple sales table with sales amounts and order dates, sorted chronologically.

Goal: Add a calculated column named RunningTotal that shows the cumulative sales up to and including each date.

Here’s the DAX formula you'd use to create the calculated column:

Running Total Sales = CALCULATE( SUM(Sales[Sales Amount]), FILTER( Sales, Sales[OrderDate] <= EARLIER(Sales[OrderDate]) ) )

Let's break this down piece by piece to see what’s happening. Remember, DAX evaluates this for every single row:

  • When the calculation starts on a specific row (e.g., the row for Jan 3, 2024), EARLIER(Sales[OrderDate]) captures that date ("Jan 3, 2024") and holds it in memory.
  • The FILTER function then iterates through the entire Sales table again, from the first row to the last. For each of these rows, it checks if the Sales[OrderDate] is less than or equal to the memorized date ("Jan 3, 2024").
  • The FILTER function returns a new, temporary table containing only the rows that meet this condition (i.e., all sales on or before Jan 3, 2024).
  • Finally, CALCULATE(SUM(Sales[Sales Amount]), ... ) sums up the Sales Amount for just that temporary table, giving you the running total for that specific row.

The process repeats for every single row, resulting in a correctly calculated running total.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Example 2: Ranking Products by Sales

Another classic use case is ranking items. Imagine you have a table of products, and you want to create a column that ranks each product from highest sales to lowest sales.

Goal: Add a calculated column named ProductRank that ranks products based on their total sales.

Pre-requisite: For this to work, you likely need a TotalSales measure or column already created for each product. For this example, let's assume we have a simple column named SalesAmount in our Products table.

Here’s the DAX formula for the ranked calculated column:

Product Rank = COUNTROWS( FILTER( Products, Products[SalesAmount] > EARLIER(Products[SalesAmount]) ) ) + 1

This looks intimidating, but the logic is surprisingly simple:

  • For the row you’re on (e.g., "Product A" with $5,000 in sales), EARLIER(Products[SalesAmount]) captures and "remembers" its sales value ($5,000).
  • Just like before, the FILTER function then scans the entire Products table.
  • It compares every product's SalesAmount to the remembered value ($5,000). It creates a temporary table of all products that have sales greater than "Product A".
  • COUNTROWS simply counts how many rows are in that temporary table. If three products had higher sales, COUNTROWS returns a result of 3.
  • Finally, we add + 1. If three products have sales greater than "Product A", its rank is 4. For the top-selling product, zero products will have higher sales, so 0 + 1 gives it the correct rank of 1.

EARLIER vs. Variables (VAR): The Modern Approach

While EARLIER is powerful, the DAX community and experts like Marco Russo and Alberto Ferrari of SQLBI generally recommend using variables (VAR) instead. Variables accomplish the same goal — capturing a value from an outer row context — but in a way that is vastly more readable and often more performant.

Complex DAX can quickly become "nested spaghetti code," and EARLIER can make it harder to follow the logic. Variables clean this up significantly.

Let's refactor our Running Total example using a variable:

Running Total Sales (VAR) = VAR CurrentRowDate = Sales[OrderDate] RETURN CALCULATE( SUM(Sales[Sales Amount]), FILTER( ALL(Sales), Sales[OrderDate] <= CurrentRowDate ) )

Here's why this is better:

  1. Clarity: VAR CurrentRowDate = Sales[OrderDate] explicitly tells you what you're storing. When you see CurrentRowDate in the filter condition, you know exactly what value it holds. It reads more like plain English and removes the mental gymnastics of thinking about "outer" versus "inner" contexts.
  2. Control: Notice the use of ALL(Sales). In the original EARLIER version, a row context implicitly exists. When using a VAR, best practice is to remove that initial filter context with ALL() to avoid unexpected results and ensure your filter is being applied to the entire table as intended.
  3. Performance: The DAX engine is often better at optimizing formulas that use variables compared to those relying on EARLIER. For very large datasets, using VAR can result in noticeably faster report performance.

The takeaway? It's important to learn what EARLIER does because you will encounter it in older Power BI reports or in online examples. However, when writing new DAX from scratch, you should almost always prefer variables.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Common Pitfalls and How to Avoid Them

As you get started, you might run into a few common issues with EARLIER:

  • Using EARLIER in a Measure: Measures don’t have a natural row context. Therefore, EARLIER will return an error unless you use it inside an iterating function like SUMX or FILTER, which create a temporary row context for the measure to use.
  • Forgetting a Better Alternative Exists: If a formula with EARLIER is getting hard to read or is acting slow, stop and ask: "Can I rewrite this more clearly using a variable?" The answer is almost always yes.
  • Nested Context Confusion: Misusing the optional number parameter (e.g., EARLIER(Column, 2)) can quickly lead to incorrect results if you don't fully understand the multiple layers of row context you are navigating. Whenever possible, simplify your calculation instead of relying on deeply nested EARLIER calls.

Final Thoughts

Understanding the EARLIER function is a significant step in your DAX journey, unlocking powerful row-level calculations like running totals and customized rankings. It’s a core concept that teaches you how different evaluation contexts interact in Power BI, but for your day-to-day work, try to embrace variables as a cleaner, more readable, and generally faster alternative.

Creating complex reports and calculations in tools like Power BI can quickly take over your day. Instead of sinking hours into wrestling with DAX, we built a tool that handles the analysis for you. We created Graphed to let you connect all your data sources instantly and generate real-time dashboards just by asking questions. This approach converts hours of manual report building into simple, 30-second conversations, freeing your team to focus on finding insights, not wrestling with formulas.

Related Articles