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

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

<tbody>
</tbody>

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] )
    )
)