Hi Sam,
That is pretty close to what I had.
Strange that when I split it out into a different measure to calculate the cumulative it works, but in the same on it does not.
As long as it works.
That just leaves the bizarre behaviour of the very original query we started off with where we try to calculate the next year base levels.
To recap. We start with the current count of students, lets say “Count Boys”, then add the entrants and remove the leavers to give the base.
To make it more complicated, we have to be able to flip between expected and confirmed entrants.
I have created a supporting table and measures for this.
I have modified the original PY Boys from
PY Boys =
VAR PY = [Previous Year Level]
VAR ProcessDates = VALUES( 'Student Monthly Statistics'[Process Date] )
VAR PYCount = CALCULATE( [Count Boys], FILTER( ALL( 'Student Monthly Statistics' ), 'Student Monthly Statistics'[Year Level] = PY ), ProcessDates )
RETURN
IF( OR ( ISBLANK( [Previous Year Level] ), SELECTEDVALUE( Gender[Gender] ) <> "Male" ),
BLANK(),
PYCount )
to now contain the EOY forecast
PY Boys =
VAR PY = [Previous Year Level]
VAR ProcessDates = VALUES( 'Student Monthly Statistics'[Process Date] )
VAR PYCount = CALCULATE(
[EOY Forecast],
FILTER(
ALL( 'Student Monthly Statistics' ),
'Student Monthly Statistics'[Year Level] = PY && 'Student Monthly Statistics'[Gender] = "M"
),
ProcessDates
)
RETURN
IF(
OR (
ISBLANK( [Previous Year Level] ),
SELECTEDVALUE( Gender[Gender] ) <> "Male"
),
BLANK(),
PYCount
)
The only difference here being the filter for male students and
EOY Forecast = [Count Students] + [Entrants] - [Leavers]
Entrants =
SWITCH(
TRUE(),
'Forecast Selection'[Forecast Selection] = "Confirmed", [Confirmed Entrants],
'Forecast Selection'[Forecast Selection] = "Forecast", [Forecast Entrants],
[Forecast Entrants]
)
Leavers =
SWITCH(
TRUE(),
'Forecast Selection'[Forecast Selection] = "Confirmed", [Confirmed Leavers],
'Forecast Selection'[Forecast Selection] = "Forecast", [Forecast Leavers],
[Forecast Leavers]
)
For reasons I cannot fathom it works perfectly when using the confirmed results, but goes haywire when using the forecast results.
It makes no sense as the PY Boys measure is referencing the same measure.
Attached is the cleaned up file.
Thanks
Fun.pbix (928.2 KB)