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:
![](https://practicepowerbi.com/wp-content/uploads/2021/08/0028-basic-Total-Sum-measure-1.png)
Below, a Month-to-Date running total. TotalSales shows individual amounts.
![](https://practicepowerbi.com/wp-content/uploads/2021/08/0028-Total-Sum-measure-with-running-total.png)
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.
![](https://practicepowerbi.com/wp-content/uploads/2021/08/0028-no-reset.png)
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.
![](https://practicepowerbi.com/wp-content/uploads/2021/08/0028-RT-starting-with-1st-viz-date.png)
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.
![](https://practicepowerbi.com/wp-content/uploads/2021/08/0028-RT-MTD-Last-Year.png)
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.
![](https://practicepowerbi.com/wp-content/uploads/2021/08/0028-custom-start-stop-with-total.png)
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.
![](https://practicepowerbi.com/wp-content/uploads/2021/08/0028-stop-RT-before-selected-amount.png)
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.
![](https://practicepowerbi.com/wp-content/uploads/2021/08/0028-stop-before-target-amount.png)
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.