I have a fact table from a school that contains student information for each student for each day over the last 10 years. Approximately 1000 records per day over 10 years. I have filtered this in Query Editor to look at only the records for the last day of the month for EOM statistics.
I have created measures to Count Students / boys / girls
/// Count Students = COUNT('Student Monthly Statistics'[Student ID]) Count Girls = CALCULATE([Count Students], 'Student Monthly Statistics'[Gender] = "F") ///
What I need to do is project the starting number of students for the next year. This is based on using the Previous Year Value to find the current total as a starting value.
Naturally, for the earliest year level there is no previous year level (blank).
I am using the SELECTEDVALUE function for this
/// Next Year Base Girls = VAR SelectedPreviousYearLevel = SELECTEDVALUE('Student Monthly Statistics'[Previous Year Level]) RETURN IF (ISBLANK(SelectedPreviousYearLevel), BLANK(), CALCULATE( [Count Girls], FILTER( ALLSELECTED('Student Monthly Statistics'), NOT ISBLANK(SelectedPreviousYearLevel) && SelectedPreviousYearLevel = 'Student Monthly Statistics'[Year Level] ) ) ) ///
This has resulted in the following table
As can be seen, the count has been brought forward from the previous year level and the first row is empty as expected. What is missing is the total at the bottom.
The question is how can I get this sum as I need it? I suspect the problem is in the use of the SELECTEDVALUE function.