Lee Hawthorn

With PowerPivot there is a concept called Row Context.  The Row Context consists of a single row of a table.   Certain DAX functions have an iteration function.  One of these is SUMX().  The SUMX function iterates over the rows of the table and aggregates the values found in the column you specify.

When you create nested expressions in DAX you will come across nested Row Contexts. You can think of the first row context as a loop with the second row context being an inner loop.  As per the diagram below.

This is all fine, however, by default the row contexts are not aware of each other, hence, if you want to refer to the value in the outer loop within an expression from the inner loop then you need to use the function EARLIER().

The EARLIER function takes two parameters, the first is the column we want to refer to and the second is the number of tabe scans to go back, the default is set to 1.  There is also a function called EARLIEST which as you might have guessed refers to the outer most table scan.  These functions are mainly used in calculated columns  created from the Power Pivot window.  To illustrate the function I’ve created a data set consisting of weekly sales.

We’ll create a new column to calculate the cummalative sales

=sumx(
filter(Table1,Table1[Week]<=earlier(Table1[Week],1)),
Table1[Sales]
)

The SUMX function creates the first table scan.  The FILTER function creates the second table scan.   Let me describe what is happening here.  In the first table scan we get to row 1 and the filter function is executed which filters the rows based on the week number being less than or equal to the current row.  The Sale column for the filtered rows is then aggregated.

As the SUMX function gets to the second row the process is repeated.  This time the FILTER function is returning weeks < =  2 hence the prior week is included in the row set.

This is repeated during the rest of the table scan resulting in a aggregation that covers an increasing range.

I’m using this cummalative calculation to demonstrate the EARLIER function.   There are better ways to create cummalative calculations with the time intelligence features of DAX so use this example as a learning aid.

I hope you’ve found this post useful.  This is an advanced topic so don’t be concerned if you didn’t understand at the first read.   Re-create the example using your own data set and you should hopefully get the gist of what is happening.

Over and out.

Lee