I’ll review some basic DAX running totals (aka cumulative totals) and then various advanced examples.

Files: PBIX

Tables Sales and Date created using DAX.

Running Total

Concept

A viz that displays a date with a Total Sales measure can’t by default display a running total. It will only show the sum for each individual date like this:

Below, a Month-to-Date running total. TotalSales shows individual amounts.

Basic Running Total Examples

Core Sum Measure

Many of my running total measures reference this basic sum measure:

TotalSales = sum(Sales[Sale Amount])

Month To Date

DATESMTD is a built-in function. Running total resets each month. We saw this one in the pic above.

RT MTD = CALCULATE([TotalSales],DATESMTD('Date'[Date]))

There’s also a DATESYTD function (Year to Date).

No Reset

It sums until the final date with sales in the visual. Variable CurrentDate harvests the current viz row date and uses it inside CALCULATE function.

RT no reset = 
//variable retrieves current row viz date
VAR CurrentDate = max('Date'[Date])
RETURN
CALCULATE([TotalSales],'Date'[Date] <=CurrentDate)

Below we see how large measure RT no reset gets as it continues to sum.

Advanced Running Total Examples

1st Viz Date

Viz and running total start on same date. I combine filter pane date with function ALLSELECTED.

RT 1st viz date = 
//variable retrieves current row viz date
VAR CurrentDate = MAX('Date'[Date])

VAR Start1stVizDate = CALCULATE([TotalSales],
                                FILTER(ALLSELECTED('Date'),
                                'Date'[Date] <=CurrentDate))
return
Start1stVizDate

Filter pane Date set to >= July 1 2020 isn’t enough. ALLSELECTED is also required.

Without ALLSELECTED the measure on July 2 2020 starts with the entire sum up to that date. Note: July 1 had no sales.

Month To Date Last Year

Compare current month to date running total to last year’s amount. 2 methods:

Method A:

Calculate total sales for last year as a base measure…

TotalSales LY = CALCULATE([TotalSales], SAMEPERIODLASTYEAR('Date'[Date]))

…then add the running total measure that reference’s base measure above:

RT MTD LY (A) = CALCULATE([TotalSales LY],DATESMTD('Date'[Date]))

Method B:

Combine SAMEPERIODLASTYEAR and DATESMTD and reference Totalsales.

RT MTD LY (B) = CALCULATE([TotalSales],SAMEPERIODLASTYEAR(DATESMTD('Date'[Date])))

Below, I included TotalSales and RT MTD for reference.

Custom Start and Stop

Start/stop running total based on date variables (no Filters pane). I used seven variables to test carefully. Practical? Maybe not but a great challenge!

RT custom start stop with Total CORRECT = 
//currentdate retrieves current row viz date
var currentdate = MAX('Date'[Date])

//startdate & endate define start & stop dates
var startdate = DATEVALUE("07-06-2020")
var enddate = DATEVALUE("07-12-2020")

var Start1stVizDate = CALCULATE([TotalSales],
                                FILTER(ALL('Date'),
                                'Date'[Date] >=startdate && 'Date'[Date] <=currentdate))

var rowcalc = if(currentdate<startdate,BLANK(),  if(CurrentDate>enddate,BLANK(),Start1stVizDate))
var totalrow = CALCULATE([TotalSales],'Date'[Date] >=startdate, 'Date'[Date] <=enddate)
var final = if(HASONEVALUE('Date'[Date]),rowcalc,totalrow)

return
final

1st viz: running total. 2nd viz: individual daily amounts to verify running total.

Stop at Target

Stop a running total in a viz before a target amount. I created a parameter so target amount can be selected in a slicer.

Below, table created for what-if parameter. Has start, end, and increment amounts:

RT Target = GENERATESERIES(20000, 60000, 5000)

Measure below automatically created to harvest parameter value. Default = 26000:

RT Target Value = SELECTEDVALUE('RT Target'[RT Target], 26000)

Final measure uses harvested value to stop running total before selected amount.

RT stop at target = 
//currentdate retrieves current row viz date
var currentdate = MAX('Date'[Date])

//get value from parameter
var targetval = [RT Target Value]

//main calculation
var RT = CALCULATE([TotalSales],
                   FILTER(ALLSELECTED('Date'),
                   'Date'[Date] <=currentdate))

var final = if(RT<targetval,RT,  BLANK())
            
return
final

Below we see selected slicer amount of 35000. Displayed in a card and used in measure ‘RT stop at target’. Change slicer amount to see viz grow or shrink.

Recap

While creating the running totals I practiced these functions/features:

• variables (var) were helpful to split logic into smaller parts
• CALCULATE: an expression modified by filters
• ALLSELECTED: to start running total using first viz date
• SAMEPERIODLASTYEAR: time intelligence function to jump back a year
• IF: show/hide measure value
• HASONEVALUE: to check if row is total row
• What-if parameter: easy target selection and use value in measure

There are endless running total variations but this is enough for now. Measure Custom Start and Stop was the most challenging but I finally solved it! 🙂

Theory alone isn’t enough. I use this blog to put the theory into practice.