Looking back in time: Historical Group Membership


In this post I'm going to show how look back in time at your school population on or between two dates. At Bromcom we've just added information on class, year memberships etc which we are calling 'collections' of pupils to distinguish them from the Year and Tutor Group fields on the Students table. Our database structure for this is pretty logical:

Collections : A master list of groups with IDs and a group type (today I'm using the example of Year Groups which have the CollectionTypeName "OnRollYGrp")

Collection Associates : A group memberships table that lists Collection IDs, StudentIDs and the Start and End dates of the membership of the group.

CollectionAssociates should relate to Students on SchoolStudentId and to Collections on SchoolCollectionId

If you want to play along you can download an excel file with these tables here

Count Members of a year group

The first step is to make a measure on CollectionAssociates counting members of a year group. 

HistPupils = COUNTROWS(FILTER(CollectionAssociates,RELATED(Collections[CollectionTypeName])="OnRollYGrp")

Note that we are filtering CollectionAssociates by a field on a related table (Collections) so we need the RELATED function to call that field.

Make a table visual with Collections[CollectionName] and HistPupils. Add Filters to the visual to only show Type ‘OnRollYGrp’. Obviously this shows all year group memberships of all time, so next step is dates...

Make a DateRef Table

It's table from DAX time with a really cool formula: CALENDAR. Calendar makes a table with a row for every date between a start and end date. So Modelling| New Table, formula;

DateRef = CALENDAR("01/09/2016",Today())

The start date can be anything you like (bear in mind that the shorter your range, the more user friendly your date slider so only go back as far as you need to. Today() dynamically returns today's date.

We don't need to relate this table to the others, it's just going to provide us with measures to use as comparisons.

Min / Max Data Measures

We need to make two simple measures on DateRef:

MaxDateRef = MAX(DateRef[Date])
MinDateRef = MIN(DateRef[Date])

Obviously, they give you the earliest and latest date on the table. Check them by dragging them onto the canvas as cards and they should show your start date and today. If [MinDateRef] says 1899 then you forgot to put your start date in quote marks in your CALENDAR formula.

If you add a slicer and put DateRef[Date] in it you'll see that the measures show the range selected on the slicer.

Add Dates to the HistPupils Measure

Now we have dates we can add to the HistPupils Measure’s filters. Filters can only have one condition so we need to use AND and as there’s a maximum of two conditions per AND formula we’ll have to nest an extra one:

HistPupils = COUNTROWS(FILTER(CollectionAssociates,AND(RELATED(Collections[CollectionTypeName])="OnRollYGrp",AND(CollectionAssociates[StartDate]<[MaxDateRef], CollectionAssociates[EndDate]>[MinDateRef])

The comparison can seem counter intuitive at first but think it through in reverse: we don’t want memberships that began after our period finished or finished before our period began.

There is however, a small wrinkle because EndDates can be blank in active memberships. So we need to insert an OR condition:

HistPupils = COUNTROWS(FILTER(CollectionAssociates,AND(RELATED(Collections[CollectionTypeName])="OnRollYGrp",AND(CollectionAssociates[StartDate]<[MaxDateRef], OR(ISBLANK(CollectionAssociates[EndDate]),CollectionAssociates[EndDate]>[MinDateRef])))))

Pupil Count vs Membership Count

You’ll notice that this is a count of Year Group memberships – so if a pupil is a member of two year groups at the same time then they’ll count twice. To check for this create a table visual with Pupil names and Ids and the HistPupils Measure, with the slider set to start and end today. Everyone should have a value of 1, anyone with 2 needs their record corrected. (In the top right hand corner of the visual there’s an option to export to CSV if you have a big list to check.)

If you want to allow multiple year group memberships but count unique pupils then you can amend your measure like this:

CalcHistPupils = CALCULATE(DISTINCTCOUNT(CollectionAssociates[SchoolStudentId]),FILTER(CollectionAssociates,AND(RELATED(Collections[CollectionTypeName])="OnRollYGrp",AND(CollectionAssociates[StartDate]<[[MaxDateRef]], OR(ISBLANK(CollectionAssociates[EndDate]),CollectionAssociates[EndDate]>[[MinDateRef]])))))

Although, be aware that depending on the row context you could still show the same pupil twice. For example, let’s say we have school with 50 pupils in Y7 and 50 in Y8 but one Y8 pupil has a membership of Y7 as well. Our measures behave like this:

School
HistPupils
CalcHistPupils
YourSchoolName
101 (counts mems)
100 counts unique pupils.

But….

Year
HistPupils
CalcHistPupils
7
51 (counts mems)
51 counts unique pupils in Y7
8
50
50







Comments

Post a Comment

Popular posts from this blog

Attainment 8 in BI - Part 1

Image
It’s been bugging me for a while that I didn’t know how to calculate A8 in BI, even though as a mostly Bromcom user it wasn’t a big deal as you can get the figures straight out of the MIS. That said, it was interesting solving all the steps along the way and even if you do get these figures out of your MIS or an Excel broadsheet I think you’ll find the exercise useful for building your BI skills. Source Data I’m going to assume source data in the form of a table of exam results with a student identifier, season, result date, exam code and result. You can download my source data  and/or the PBIX file . In a BI4Schools first we’re not going to import this data as is though, as we’ll be amending it using Power Query. Power Query (PQ) is the set of tools BI uses to get and manipulate data before placing it as a table in the BI model. PQ uses a language called M to do this – note that it is different to DAX, the language used within the BI model. To open Power Query click...
Read more

Subject - Class - Pupil Table

Image
One of the really powerful features of BI is being able to drill very quickly from the macro picture to the micro. To help schools do this with assessment results I've recently been working on this table showing subject, class and pupil level results (p2 of the report below). You can download the PBI file here , the class information looks like this  In a previous post I described concatenating studentid and subject to link classes, assessments and students, but that's not actually best practice - you can do it more simply with this setup I created the Subjects table using Dax (create table on the modelling tab). The formula is  Subjects = DISTINCT(AssessmentResults[Subject]). It gives a one column table with each distinct value of subject.  Note that here it is assumed that StudentClasses[Subject] isn't always the same AssessmentResults[Subject], as for example English Classes will often have English Literature Results. To handle...
Read more

Data modelling for a large MAT

Image
The wrong way When I first started developing in BI I worked on single schools and MAT demo systems with tiny numbers of students so my approach to data modelling was chuck data in however it comes then play with relationships until the configuration works. When I switched to developing for a large MAT (c40k kids in 50 schools) I found out the hard way the limitations of this approach-my measures and reports were unusably slow.  As well as the sheer volume of data I had problems because the MAT wanted a report on pupil demographics, attendance and exclusions with historical accuracy- ie not only seeing data for who's FSM, PP etc today but for any point/period in the past as well. This divides your pupil contextual factors into two types; unchanging, like ethnicity, EAL & KS2 prior attainment that don't change and so can stay on the students table, and changeable, like Pupil Premium eligibility, that can vary depending on the date range you're looking at.  My original ...
Read more