PowerBIforSchools

In this post I’m going to talk through some issues I’ve had and tricks come up with when dealing with national attendance stats. The Data First step is to get the data, which you can find at https://www.gov.uk/government/collections/statistics-pupil-absence (or I just google ‘dfe absence statistics’). There are three releases a year. The Autumn term release, which comes out the following May. The Autumn and Spring release which comes out the following October and the full year release which comes out the following March-June. So for the 2018-2019 academic year the autumn term stats were released in May 2019, the Autumn-Spring will be release this month (October 2019) and the full year stats will come out in Spring 2020. You therefore usually have a choice between more recent data that doesn’t cover the whole year and older data that does. Another factor to bear in mind is that the Autumn-Spring data doesn’t have breakdowns for Ever6 FSM where as the full year does. T

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 (a