How to use the EARLIER function in DAX to sort, group, band and accumulate data

Part two 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.

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.

Running totals using the EARLIER function in DAX

To get this example to work, first bring the PosDate column
from the Pos table into the Transaction table:

Use the
RELATED function to bring the point-of-sale date for each transaction into the transactions table.

The answer we're aiming for

When travelling, it helps to keep the destination in sight. Here's what
we want to achieve:

We'll create a new column giving the cumulative quantity (although this makes more sense if you sort the transactions by date, as below).

If you sort the transactions into date order, the figures make more sense:

Because the database doesn't record the time of the transaction, figures for each day's sales are lumped together.

The formula to achieve this

Here's a formula which would achieve this:

=SUMX (

FILTER (

'Transaction',

'Transaction'[PosDate] <= EARLIER ( 'Transaction'[PosDate] )

),

'Transaction'[Quantity]

)

That is, instead of summing the quantity for the current row's transaction, sum it instead across the set of those rows whose transactions take place on a date up to and including the transaction date for the current row.

Don't forget (I often do) that this has to be the formula for a calculated
column, and not a measure. Because it's referring to the value of the
PosDate for the current row, a measure would make no sense and
would return an error.