### Historical Memberships 2

In a previous post I covered how to show historical
membership dates, but that referred specifically to the Bromcom MAT Vision
dataset, and I’ve since found out a more elegant way of writing the measures,
hat tip @JonnySBristol so I’ll recap briefly in the first section of this post,
then cover some new techniques in sections 2 and 3.

1 Calculate historical memberships from an enrolments table.

My sample data set is structured as a students table with
ID, name and gender and an enrolments table with studentid, StartDate and
EndDate. This is better than having date of entry/leaving on the students table
because it can accurately record a student leaving, then rejoining school.

As well as the student and enrolments table we create a DateRef
table, via modelling New Table (more details in this in post 1).

Table from DAX

DateRef = CALENDAR(“01/01/2016”,TODAY())

Measures:

MaxDate = MAX(DateRef[Date])

MinDate = MIN(DateRef[Date])

We don’t need to relate DateRef (although we can, and I usually
directly link it to my attendance table). All we need are MaxDate and MinDate
which we’ll dynamically set using a date slider on the field DateRef[Date].

Historical Memberships

Once you have this set up you can write a measure for
counting students who were on roll between your two dates.

StudentsOnRoll = CALCULATE(COUNTROWS(Students),

FILTER(Enrolments,AND(Enrolments[StartDate]<=[MaxDate],OR(Enrolments[EndDate]>=[MinDate],ISBLANK(Enrolments[EndDate])))))

CALCULATE allows us to perform an operation within the
context of additional filters we supply (note that unless you say otherwise
filters that exist from your report/page/slicers/row context will also apply).

Crucially for our purposes here, those filters can be
applied to related tables and passed the table we are calculating. So our
filter for enrolments that fall within our date range is passed to the students
to whom those enrolments belong.

Single Date Filter

If you’re not interested in a range but only students on
roll on a particular date, then change your slicer type to Before and use MaxDate
in place of MinDate in the measure.

2 Calculating PP Eligibility

Pupil Premium Eligibility
(or any other changeable contextual factor) can be worked out in exactly the
same way provided you have a table with studentids, start and end dates. We can
use our existing on roll measure as the base (so we don’t have to type out the
on roll filter again). Hence:

PPStudents = CALCULATE([StudentsOnRoll],

FILTER(PPDates,AND(PPDates[StartDate]<=[MaxDate],OR(PPDates[EndDate]>=[MinDate],ISBLANK(PPDates[EndDate]))))

)

Now moving the date slicer will show in this measure a count
of students who were on roll and eligible for the PP.

If you want to see who the kids are then just create a table
with the data you want from Students and add the PPStudents measure – by default
it will only show those where the measure is not blank (ie PP = true).

Calculating Non PP Eligibility

How about the reverse case – students who are not eligible
for the PP at a particular point in time. We can work this out using a couple
of new formulas CALCULATETABLE – which works just CALCULATE but instead of returning
a single value from a filter context it returns a table. And EXCEPT. Except
takes two tables as arguments and gives you all the rows from the first (‘left’)
table that don’t appear on the second (‘right’) table.

NotPPStudents =

CALCULATE([StudentsOnRoll],

EXCEPT(CALCULATETABLE(Students,FILTER(Enrolments,AND(Enrolments[StartDate]<=[MaxDate],OR(Enrolments[EndDate]>=[MinDate],ISBLANK(Enrolments[EndDate]))))),

CALCULATETABLE(Students,FILTER(PPDates,AND(PPDates[StartDate]<=[MaxDate],OR(PPDates[EndDate]>=PPDates[StartDate],ISBLANK(PPDates[EndDate]))))))

)

3 Date Sensitive Filtering

This is all fine if we know in advance what data we want to
show – but what about slicers to allow our users to filter on date sensitive
factors? You might think we could create calculated columns using our measures and
slice on them but that won’t work because calculated columns are worked out without
reference to slicer values – so they wouldn’t respond dynamically to our Min
and Max date values.

There is a fix for this but, as we’ll see, it won’t let you
go wild with the number of slicers you can place. Basically what we’re going to
do is creating a floating table a bit like DateRef and refer to the SELECTEDVALUE
from that in measure logic.

Create a table from the modelling tab:

PPSwitch = DATATABLE("PP",STRING,

{

{"Y"},

{"N"}

}

)

That’s a dead simple, 1 column table with possible values Y
or N. We don’t need to relate it to anything, just stick it on the canvas in a
slicer. The function SELECTEDVALUE returns the value from a column if it is filtered
to a single value, otherwise it returns the alternative result.

So the formula SELECTEDVALUE(PPSwitch[PP],”All”) will return
Y/N if we select them in the slicer, if neither are selected it will return All.

We can then create a measure:

SelectableStds =
SWITCH(SELECTEDVALUE(PPSwitch[PP],"ALL"),"Y",[PPStudents],"N",[NotPPStudents],"All",[StudentsOnRoll])

The logic is pretty simple with only three outcomes, but
adding more will obviously increase exponentially the number of conditions/measures
you would need (e.g. having Y/N/All for SEN would mean building in IF statements
to identify 9 separate outcomes. Hence the impracticality of having multiple
slicers with this method.

Here’s the file

## Comments

## Post a Comment