BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- The EARLIER function in DAX (this blog)
- Running totals using the EARLIER function in DAX
- Using the EARLIER function to rank data
- Group statistics using the EARLIER function
- Banding using the EARLIER function
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.