Answered by:
DAX Formula for Running Totals (Student Database)
Question

Hi all,
I'm having some issues refining a DAX measure that will calculate the running total for students in a given data set. Essentially, this is what my data look like
Table Name: STUD
Headcount Academic Year Discipline 2008/09 2009/10 2010/11 2011/12 2012/13 2013/14 2014/15 2015/16 2016/17 2017/18 Business & Applied Studies 22 28 22 22 28 28 22 28 22 222 Tourism & Hospitality Management 22 28 22 22 25 28 22 28 22 222
I'm not sure whether I need specific dates to make this function work (as most examples include specified dates), but any help would be greatly appreciated!
DarkoThursday, March 22, 2018 7:23 PM
Answers

If head count is a measure you don't need to apply the SUM aggregate function and you can just reference the measure directly.
eg.
Running Total:=VAR maxIntYear = MAXX(VALUES(Stud[AcademicYear]), INT(LEFT(Stud[AcademicYear],4)))
RETURN CALCULATE([HeadCount],
FILTER( ALL(Stud[AcademicYear]), INT(LEFT(Stud[AcademicYear],4)) <=maxIntYear))
http://darren.gosbell.com  please mark correct answers
 Marked as answer by Darko Giacomini Monday, March 26, 2018 3:15 PM
Friday, March 23, 2018 4:14 PM
All replies

You don't necessarily need dates, but it makes it easier as the pattern in DAX for a running total relies on getting "years" less than or equal to the current Max() year and you can't use the MAX() function on strings (and "2008/09" is a string value)
So assuming that your data is loaded in 3 columns:
Discipline AcademicYear HeadCount
You could write a formula like the following:
Running Total:=VAR maxIntYear = MAXX(VALUES(Stud[AcademicYear]), INT(LEFT(Stud[AcademicYear],4)))
RETURN CALCULATE(SUM(Stud[HeadCount]),
FILTER( ALL(Stud[AcademicYear]), INT(LEFT(Stud[AcademicYear],4)) <=maxIntYear))which pulls out the first 4 characters of your academic year and converts them to an Integer so that we can calculate the max value in the current context and then find all the years with a lower value.
http://darren.gosbell.com  please mark correct answers
Thursday, March 22, 2018 10:04 PM 
You don't necessarily need dates, but it makes it easier as the pattern in DAX for a running total relies on getting "years" less than or equal to the current Max() year and you can't use the MAX() function on strings (and "2008/09" is a string value)
So assuming that your data is loaded in 3 columns:
Discipline AcademicYear HeadCount
You could write a formula like the following:
Running Total:=VAR maxIntYear = MAXX(VALUES(Stud[AcademicYear]), INT(LEFT(Stud[AcademicYear],4)))
RETURN CALCULATE(SUM(Stud[HeadCount]),
FILTER( ALL(Stud[AcademicYear]), INT(LEFT(Stud[AcademicYear],4)) <=maxIntYear))which pulls out the first 4 characters of your academic year and converts them to an Integer so that we can calculate the max value in the current context and then find all the years with a lower value.
http://darren.gosbell.com  please mark correct answers
Thanks for the recommendation Darren! However in this case, "Headcount" is a measure, so I'm getting an error when I reference Headcount in the formula. I would say that I'm working with the two columns (Academic Year and Discipline).
Thursday, March 22, 2018 10:20 PM 
If head count is a measure you don't need to apply the SUM aggregate function and you can just reference the measure directly.
eg.
Running Total:=VAR maxIntYear = MAXX(VALUES(Stud[AcademicYear]), INT(LEFT(Stud[AcademicYear],4)))
RETURN CALCULATE([HeadCount],
FILTER( ALL(Stud[AcademicYear]), INT(LEFT(Stud[AcademicYear],4)) <=maxIntYear))http://darren.gosbell.com  please mark correct answers
 Proposed as answer by willson yuanMicrosoft contingent staff Friday, March 23, 2018 6:28 AM
Thursday, March 22, 2018 11:40 PM 
Hi Darko,
Thanks for your question.
Darren has provided a solid solution for you. For this issue, you can also refer to below blog:
https://www.daxpatterns.com/cumulativetotal/Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.comFriday, March 23, 2018 6:34 AM 
If head count is a measure you don't need to apply the SUM aggregate function and you can just reference the measure directly.
eg.
Running Total:=VAR maxIntYear = MAXX(VALUES(Stud[AcademicYear]), INT(LEFT(Stud[AcademicYear],4)))
RETURN CALCULATE([HeadCount],
FILTER( ALL(Stud[AcademicYear]), INT(LEFT(Stud[AcademicYear],4)) <=maxIntYear))
http://darren.gosbell.com  please mark correct answers
 Marked as answer by Darko Giacomini Monday, March 26, 2018 3:15 PM
Friday, March 23, 2018 4:14 PM 
Hey Darren, just had another question...What is the "MaxIntYear" function doing? Don't think I've come across that one yet, and I've been trying to find some literature around it, but no luck.
Thanks!
Friday, March 23, 2018 5:10 PM 
Hey Darren, just had another question...What is the "MaxIntYear" function doing? Don't think I've come across that one yet, and I've been trying to find some literature around it, but no luck.
Thanks!
That's not actually a function, it's a reference to a variable which is defined in the VAR expression. So that is effectively just a placeholder for the value calculated by the "MAXX(VALUES(Stud[AcademicYear]), INT(LEFT(Stud[AcademicYear],4)))" expression.
I find variables (using VAR and RETURN) are good for breaking more complex statements down into smaller chunks and they can also help with performance as they get evaluated once within their context.
So in this case you could rewrite the running total with out using a variable by substituting in the body of expression instead (which will also work for versions of Excel earlier than 2016).
eg.
Running Total:=CALCULATE([HeadCount],
FILTER( ALL(Stud[AcademicYear]), INT(LEFT(Stud[AcademicYear],4)) <= MAXX(VALUES(Stud[AcademicYear]), INT(LEFT(Stud[AcademicYear],4)))
))
While the above expression will produce the same result as the one using a variable, both FILTER and MAXX are iterator functions, so within each loop over the years it potentially loops over all the years again to find the max. And the max is a technique that lets this expression work for the multiple selection filters and grand totals. At the grand total level the VALUES() expression will return all years and the grand total for the running total will therefore be the running total for the last year. And if someone filters on 2014 and 2105 the running total will calculate for the maximum value (so up to 2015)
http://darren.gosbell.com  please mark correct answers
Saturday, March 24, 2018 12:08 AM