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:

101 (counts mems)
100 counts unique pupils.


51 (counts mems)
51 counts unique pupils in Y7


Popular posts from this blog

Covid-19 Monitoring

Attainment 8 in BI - Part 1

Subject - Class - Pupil Table