DAX Running Total Quick Measure verus a Human Created Measure

Dax Running Total

An example we start of we a sales and date table to crate our running table sales from.
We start by creating a sales measure as follows:

Sales = SUM(FactSales[SalesAmount])

Next we create a quick measure to create the ;Running Total in Sales’ and add it to our table:

The Code for the Quick Measure ‘Sales running total in Year’ is a follows:

Sales running total in Year = 
CALCULATE(
	[Sales],
	FILTER(
		ALLSELECTED( 'Date'[Year]),  
		ISONORAFTER( 'Date'[Year], MAX('Date'[Year]), DESC
	)
))

It looks pretty simple, but there is quite a lot to it, so let’s break it down:

1. CALCULATE() Syntax: CALCULATE(Expression, [Filter1]…)
The CALCULATE() function takes the [Sales] measure as the first argument. [Sales] is just the sum of sales.

Next, we have the main filter argument that filters the [Sales] measure on a range of years from the ‘Date’Year column.
The filter starts with the FILTER function which takes ALLSELECTED(‘Date'[Year]) as the column to filter on and the
ISONORAFTER( ‘Date'[Year], MAX(‘Date'[Year]), DESC as the filter argument.

ALLSELECTED(‘Date'[Year])
The ALLSELECTED function removes all filters external to the column ‘Date'[Year], so in our table, the Years in the year column will be ignored, so all years in the ‘Date’ table will be included in the column.
We can prove this by creating another measure:

CountrowsAllSelectedYear = COUNTROWS(ALLSELECTED( ‘Date'[Year]))

When added to our table visual, we can see the count is 24 for every year row in the table. That is because this particular table has 24 years of date information in it.

The next part is the tricky part, how we filter ALLSELECTED(‘Date'[Year])

ISONORAFTER(‘Date'[Year], MAX(‘Date'[Year]), DESC)
The ISONORAFTER function compares each element of the first argument (the ‘Date'[Year column] with the second argument, the MAX(‘Date'[Year]), which is either sorted in ascending order (ASC) or descending order (DESC).
We can check what MAX(‘Date'[Year]) evaluates to with the following measure:

Max Date Year = MAX(‘Date'[Year])

We can see in our table that the max date equals the Year in the table visual.

4.2 The second argument is MAX(‘Date'[Year]). To understand what this function does, we can create a measure as it may not be what you think.

Max Date Year = MAX(‘Date'[Year])

As you can see in the table below, the Max Date Year used in this context actually returns the same date as the Year column.

The comparison works as follows:

‘Based on the sort order, the first parameter is compared with the second parameter. If the sort order is ascending, the comparison to be done is the first parameter greater than the second parameter. If the sort order is descending, the comparison to be done is the second parameter less than the first parameter’

To table below shows what is going on (if I filter the page to years 2007-2009), which effectively filters the years in the table down from 24 to 3, we can create the following table.
Essentially it is checking if each element in the date year table is on or after the max date, which is derived from the year in the table visual. If it is less than the Max([Date] then it returns true.

The Years in the Date[Year] column are then returned as a filter to the Calculate to filter the [Sales] measure creating the ‘Running Total in Sales’

Year: 2007
Date[Year]	Max([Date])	ISONORAFTER(a,a, DESC) If first less than second	Sales Total
2007	2007	TRUE	4.56M
2008	2007	FALSE
2009	2007	FALSE
Running Total			4.56M
Year: 2008	Max([Date])	ISONORAFTER()	Sales Total
2007	2008	TRUE	4.56M
2008	2008	TRUE	4.11M
2009	2008	FALSE
Running Total			8.63M
Year :2009	Max([Date])	ISONORAFTER()	Sales Total
2007	2009	TRUE	3.74M
2008	2009	TRUE	4.11M
2009	2009	TRUE	3.74M
Running Total			12.41M

That’s pretty complicated if you ask me, even though it is correct.
We can create our own Running Total in Sales Measure, that is easy to understand.
The code is below:

Running Total human = 
VAR the = SELECTEDVALUE('Date'[Year])
VAR MaxDateInFilterContext = MAX ('Date'[Year])
VAR DatesLessThanMaxDate =
    CALCULATE([Sales], 'Date'[Year]<= MaxDateInFilterContext)
RETURN
DatesLessThanMaxDate

And the output looks good.