How to Use EARLIER Function in Power BI

Wading into the world of DAX in Power BI can sometimes feel like learning a new language, and functions like EARLIER are a perfect example. While it might seem confusing at first, understanding this function is a key step to unlocking more powerful calculations. This tutorial will walk you through exactly what EARLIER does, how it works, and how to use it in practical scenarios to level up your Power BI reports.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What Are Row Context and Filter Context?

Before we can even talk about EARLIER, we need to grasp two foundational concepts in DAX: row context and filter context. Almost every DAX confusion stems from not understanding the difference between these two.

Row Context

Think of row context as the DAX engine saying, "I am currently looking at this specific row." It exists when you are iterating through a table, one row at a time. The most common places you encounter row context are:

• Calculated Columns: When creating a calculated column, the formula is evaluated for each row of the table individually. That individual row is the row context.
• Iterator Functions: Functions that end in "X" (like SUMX, AVERAGEX, FILTER, COUNTX) create their own row context. They loop through a specified table and perform a calculation for each row.

For example, if you have a Sales table and create a calculated column Total Price = Sales[Quantity] * Sales[Unit Price], DAX is performing that calculation one row at a time, using the values from the current row context.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Filter Context

Filter context is the set of active filters being applied to your data model before a DAX calculation even begins. These filters come from:

• Slicers and filters on your report page.
• The rows and columns of a visual (like a matrix or bar chart).
• Other DAX measures using the CALCULATE function.

For example, if you have a card visual showing total sales and you select "2023" in a Year slicer, the filter context for that calculation is Year = 2023.

Understanding this distinction is crucial because EARLIER is all about navigating different – and sometimes nested – row contexts.

What Does the EARLIER Function Actually Do?

At its core, the EARLIER function helps you break out of the current row context to grab a value from an outer or "earlier" row context. This is most useful when you have nested iterators – for example, a FILTER function operating inside a calculated column.

The syntax is straightforward:

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

• <column>: The column whose value you want to retrieve.
• [<number>] (optional): This tells DAX how many layers of row contexts to jump "out" of. If you leave it blank, it defaults to 1, which is what you'll use 99% of the time.

Imagine you're in a room (the inner row context) inside a larger building (the outer row context). EARLIER lets you reach outside the door of your room and grab something from the building floorplan.

Example 1: Calculating a Running Total

Creating a running total is a classic Power BI task and a perfect way to demonstrate EARLIER. Let's say we have a simple sales table with chronological sales data like this:

Sales Table

Our goal is to add a calculated column, Running Total, that shows the cumulative revenue up to and including that row's date.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step-by-Step Instructions

1. In Power BI, go to your Data view and select the 'Sales' table.
2. In the 'Table tools' tab, click 'New column'.
3. Enter the following DAX formula:

Running Total = CALCULATE( SUM(Sales[Revenue]), FILTER( ALL(Sales), Sales[Date] <= EARLIER(Sales[Date]) ) )

Let's break down how this works row by row:

• The formula starts on the first row (Jan 5th). This is our outer row context.
• The FILTER function begins. It creates a new, inner row context as it iterates over a copy of the entire Sales table (thanks to ALL(Sales) which removes existing filters).
• Inside the FILTER, it compares the date from the inner context (Sales[Date]) with the date from the outer context (EARLIER(Sales[Date])).
• For the first row (Jan 5th), EARLIER(Sales[Date]) is '2023-01-05'. The filter keeps only rows with a date less than or equal to Jan 5th, which is just that one row. CALCULATE then sums the revenue for this row, resulting in $100.
• On the next row (Jan 10th), EARLIER(Sales[Date]) becomes '2023-01-10'. The filter now keeps the rows for Jan 5th and Jan 10th. CALCULATE sums their revenue, resulting in $250 ($100 + $150).

The process continues, using EARLIER to always reference the date of the row the column is currently being calculated for. Your resulting table will look like this:

Example 2: Ranking Within Categories

Here's another powerful use case for EARLIER: ranking rows based on a value within their own specific category. Imagine our sales table also has a Category column.

Our goal is to create a column that ranks each sale by revenue, but only against other sales in the same category.

Rank in Category = 
COUNTROWS(
    FILTER(
        Sales,
        Sales[Category] = EARLIER(Sales[Category]) &&
        Sales[Revenue] >= EARLIER(Sales[Revenue])
    )
) + 1

Breaking it down:

• The formula evaluates on a row-by-row basis (outer context).
• The FILTER function starts its own iteration (inner context) over the entire sales table.
• First Condition: Sales[Category] = EARLIER(Sales[Category]). This is the magic. It checks if the category of the row the FILTER is looking at (inner context) is the same as the category of the original row (outer context). This ensures we only rank within the right group.
• Second Condition: Sales[Revenue] >= EARLIER(Sales[Revenue]). This finds all sales in that category with revenue greater than or equal to the current row's revenue.
• COUNTROWS then counts how many rows met both criteria. The sale with the highest revenue will have a count of 1, the second highest a count of 2, and so on, creating a rank.

Graphed

Still Building Reports Manually?

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

Watch the Demo

A Better Way: Using Variables Instead of EARLIER

While understanding EARLIER is vital for reading older code and grasping DAX concepts, modern DAX offers a better, faster, and more readable way to achieve the same results: variables.

Variables (declared with VAR) allow you to store a value from the initial row context and then use it anywhere inside your formula. This completely removes the need for EARLIER.

Let's rewrite our running total formula using a variable:

Running Total (VAR) = 
VAR CurrentDate = Sales[Date]
RETURN
CALCULATE(
    SUM(Sales[Revenue]),
    FILTER(
        ALL(Sales),
        Sales[Date] <= CurrentDate
    )
)

Here, VAR CurrentDate = Sales[Date] captures the date from the outer row context and stores it. The CurrentDate variable is now available inside the FILTER function, working exactly like EARLIER(Sales[Date]) but in a way that’s much clearer to read and more efficient for the Power BI engine to process. As a best practice, you should favor variables over EARLIER in your new formulas.

Final Thoughts

The EARLIER function in DAX is a powerful tool for manipulating nested row contexts, essential for complex calculations like running totals and conditional ranking directly in calculated columns. While its functionality has largely been superseded by more efficient and readable variables, understanding how it works remains a key piece of the Power BI puzzle.

Writing complex DAX can often be the biggest slowdown for marketing, sales, and business teams who just need clear answers from their data. This is where we designed Graphed to help. Instead of wrestling with DAX functions or context transitions, you can connect your data sources – like Google Analytics, Salesforce, or Shopify – and simply ask Graphed to build your dashboards and reports in plain English. We turn hours of formula writing into a 30-second task, so you can skip right to the insights.