# 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!

Darko
Thursday, March 22, 2018 7:23 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.

Thanks so much Darren, worked like a charm!
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)

You could write a formula like the following:

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.

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)

You could write a formula like the following:

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.

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.

Thursday, March 22, 2018 11:40 PM
• Hi Darko,

Darren has provided a solid solution for you. For this issue, you can also refer to below blog:
https://www.daxpatterns.com/cumulative-total/

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.com

Friday, 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.

Thanks so much Darren, worked like a charm!
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 re-write 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.