WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 482 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

How to use the EARLIER function in DAX to sort, group, band and accumulate data
Part one of a five-part series of blogs

Although the EARLIER function in DAX is complicated, it's also very useful! This blog shows how the function works, and how to use it to create running totals, sort rows, create group statistics and divide data into bands.

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 13 January 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# The EARLIER function in DAX

This is not only one of the most powerful functions in SSAS Tabular; it's also one of the most complicated.

The basic problem with this function is that it has a really confusing and misleading name!  The EARLIER function allows us to refer to the current row while deciding which rows to pick to aggregate. Perhaps it should be called the CURRENTCONTEXTROW function?

## What this blog will show

In this blog I'll show how to use the EARLIER function to create the following:

Example What it asks to do
Running totals Show for each row the cumulative total of all of the values with dates up to and including this row's date.
Ranking Show for each row the number of rows whose price is more than this row's price.
Grouping Show for each row the average price for all of the products whose target gender is the same as this row's target gender.
Banding Find out in which price band each row lies.

## Summary of how EARLIER works

Reading through the examples above, you'll recognise that they all refer to each row.  Here's how the EARLIER function works:

For each row of a table, SSAS Tabular aggregates data over the entire table, depending on the values of fields for the row in question.

If you know SQL, you may recognise that the EARLIER function is the equivalent of an SQL correlated subquery (although the database engine in SSAS will ensure that calculations using EARLIER run much more quickly than do the equivalent correlated subqueries in SQL).

Let's start with a look at how to create running totals in SSAS Tabular, using the EARLIER function.