locked
DAX Formula for Running Totals (Student Database) RRS feed

  • 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

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

    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)

    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

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

    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

    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.                 

    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