DAX Formula for Running Totals (Student Database)

[GiacTorrence]

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

[TABLE="width: 643"]
<tbody>[TR]
[TD]Headcount[/TD]
[TD]Academic Year[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Discipline[/TD]
[TD]2008/09[/TD]
[TD]2009/10[/TD]
[TD]2010/11[/TD]
[TD]2011/12[/TD]
[TD]2012/13[/TD]
[TD]2013/14[/TD]
[TD]2014/15[/TD]
[TD]2015/16[/TD]
[TD]2016/17[/TD]
[TD]2017/18[/TD]
[/TR]
[TR]
[TD]Business & Applied Studies[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]222[/TD]
[/TR]
[TR]
[TD]Tourism & Hospitality Management[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]222[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

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

 

[gazpage]

Your data isn't very well structured for use in Power Pivot. You want to use the Query Editor to unpivot the data so that you have only three columns; Discipline, Academic Year, and Headcount. You also want your Academic Year column to be a number or a date, if it's really important that the year be formatted like that then add a calendar table that does the conversion.

I'm not quite sure I understand what a running total of headcount looks like, but assuming you just want to add the figures for prior year then you just want something like.

:=CALCULATE (
    SUM ( STUD[Headcount] ),
    FILTER (
        ALL ( STUD[Academic Year] ),
        STUD[Academic Year] <= MAX ( STUD[Academic Year] )
    )
)