Supercharging your attendance data

For school leaders and pastoral teams it is especially important to understand and have easy access to attendance data. At my school this data has always been available in ISAMS, but finding it isn’t immediately obvious and custom reports, if you know how to generate them, aren’t dynamic. This makes it harder, or at least more time consuming to understand a pupils attendance record. Thankfully, this is where PowerBI has the potential to shine! Here is one of the completed attendance report pages. The rest of the post will help to outline how I got here.

Attendance and lateness dashboard for schools data analysis in Power BI
The lateness (and attendance) dashboard for school registration data in PowerBI.

Defining the purpose of a report

When building any data visualisation, the first step is ascertaining the questions we want to answer using the data. With attendance data these questions include:

  • What is the overall attendance % for the school?
  • How does the attendance % look in different parts of the school (school section, year group or academic house)?
  • For what reasons are pupils absent?
  • Do certain groups of pupils have¬†significantly different attendance compared to the average?
  • How does attendance in this time period compare to another time period (last year, last term or last month for example)?
  • Are there any patterns for when attendance is lower (days of the week, months of the year etc.)?
  • Is attendance getting better or worse over time?
  • How punctual are our pupils?
  • Which pupils are frequently late?
  • Which pupils have both an attendance and lateness issue?
  • Are there any patterns or trends in either how late pupils are, or how often they are late?

Understanding the data model

The next step is to understand the data model and where all the data you need comes from. For the questions outlined above, the relevant data comes from three tables which are connected as shown below:

The tables used for the attendance data report pages. The LinkTable isn’t needed for this report, however, it exists in my model for other reports and thus you can see it here. To build your own model for registration data, you can just create the relationships from the date to registration table using the date field to connect them, and student ID to connect the student info table to the registration table.

Firstly, the student info table contains the students name, cohort, academic house plus other relevant characteristics which you might decide are relevant lenses of analysis such as SEN, EAL or nationality.

The dates table contains the dates but also associated date info for example the academic year and term name.

Finally, the registration data table contains a unique row for each pupils registration result for each day that they are enrolled at our school. This also includes an absence reason (if absent) and the number of minutes late (if a pupil is marked as late).

The date and student info and dimension tables whilst the registration data table is a fact table. There is a 1 to many relationship between dimension tables and fact table.

Creating the measures

Once we understand what the data we are playing with looks like, it’s time to create the measures that will enable us to answer the questions we established. There are quite a few measures involved in my solution and to avoid complicating the process I always start with the base measures. These are simples measures that you can then go on to use in other measures. For example, calculating attendance % uses the following:

// Count the total number of registrations (1 student on one day = 1 registration)
Total no. of student school days = COUNT('Morning registration data'[RegistrationResultValue])

// Count the number of students marked as present
Number of students physically present = CALCULATE (
    COUNT ( 'Morning registration data'[RegistrationResultValue] ),
    'Morning registration data'[RegistrationResultValue] = 1
)

// Count the number of students marked as absent (not physically in school), but on a school related activity e.g. sports fixture
Number of school related absences = CALCULATE (
    COUNT ( 'Morning registration data'[School related absence] ),
    'Morning registration data'[School related absence] = "Y"
)

// Calculate all students who are present onsite or on an offsite school related activity
Number of students present (on and off-site) = [Number of students physically present]+[Number of school related absences]

// Calculate attendance %
Attendance % = Divide (
    [Number of students present (on and off-site)],
    [Total no. of student school days],
    0)

To calculate the number of late days per student per month is a little more involved. It looks like this:

// Count all the students who are late (defined as greater than 1 minute late)
Count of late students = CALCULATE(
          COUNT('Morning registration data'[LateMinutes]),
          FILTER('Morning registration data',
          'Morning registration data'[LateMinutes]>=1)
          )

// Create a calculated column to show the number of months a pupil has been at Wellington. 
Months in school = IF('Student info'[Current Student]="Yes",
                        DATEDIFF('Student info'[StudentEnrolmentDate].[Date],
                        TODAY(),MONTH),
                        DATEDIFF('Student info'[StudentEnrolmentDate].[Date],
                        'Student info'[LeavingDate].[Date],MONTH)
                    )

// Calculate the average number of months at Wellington
Average months at Wellington = AVERAGE('Student info'[Months in school])

// Calculate the number of months difference in the date range selected
Number of months difference = DATEDIFF(MIN(Dates[Date]), MAX(Dates[Date]),MONTH)

// Calculate how many students are late per month. The first IF statement just ensures that 0 is returned rather than 'blank' in case no students are late for the selected time period. The second IF statement determines the denominator for the DIVIDE function. If a pupil who has only been at school for 2 months has been late on 12 occasions, then the calculation should return 6. Yet if the number of months in the date range selected is 12, the result will be 1 (12 late occasions divided by 12 months). We need to use the smaller of these two month values and the second IF statement helps to do that.

Late students per month = IF(
        [Count of late students]=0,0,
            DIVIDE([Count of late students],
                IF([Number of months difference]>=[Average months at school],
                    [Average months at school],
                    [Number of months difference]),
                    0)
                )

// How many students are there?
Number of students = Count('Student info'[StudentID])

// Finally, we do one more calculate to complete the measure.
Late days per student per month = DIVIDE(
    [Late students per month],
    [Number of students],
    0) 

The other measures are the average lateness (simply the average of the number of minutes a pupil is late) and the comparison to last year calculation. The last year calculation is actually pretty straightfoward. It looks like this for the attendance % LY measure, but the same logic applies across any other measure you’re written:

// Same period last year calculation. The CALCULATE function enables you to run an expression, in this case calculating the attendance %, for a particular filter condition. The filter applied here is to shift the year 1 year back. So, for whatever date selection we have, this measure will run the expression for the previous year. 

Attendance % LY = CALCULATE(
           [Attendance %],
           DATEADD(Dates[Date],-1,YEAR))

The final set of calculations have been used to create dynamic categories for the scatter graph. If a pupil has greater than 95% attendance and are late less than 1 day per month on average then their attendance is considered ‘good’. If attendance is less than 95%, but they are late less than 1 day per month then there is an attendance issue. If attendance is greater than 95%, but they are late more than 1 day per month then there is a lateness issue. And if attendance is less than 95%, and they are late more than 1 day per month on average then there is both an attendance and lateness issue. Unfortunately, producing this was more complicated than I can explain. However, this is where I learnt the technique if you want to give it a go.

The attendance and lateness scatter graph enables quick identification of pupils who we want to work to improve their attendance and/or punctuality.

Our aim is to get as many pupils as possible into the good attendance category and this scatter chart helps us to easily identify individual pupils we can work with to make that happen. The beauty of the categories is that we can now select one to filter the pupils shown on the chart to provide more detail. For example, upon selecting ‘attendance and lateness issue’ the chart updates to show the following:

Zooming in to show the details for one of the categories. It is quick and easy to see that we should be working on Student 388s puncuality, whilst student 298 and 88 have attendance issues that need resolved.

Using buttons to improve chart interactivity

The matrix table on the right of the page shows some key lateness statistics broken down by school group, cohort OR academic house and student name. The measures used have all been described above and they are just dropped into the values section of the visualisation panel. The buttons at above the chart make it interative by switching between showing the data broken down by student cohort (year group) or academic house. This makes it easy to analyse the data in through different analytical lenses. Do our year 10 pupils have a lateness problem or is it just pupils in x academic house?

Matrix with interactive buttons to show data by student cohort.
Upon pressing the academic house button the matrix has changed to organise the data by academic house rather than student cohort. Note that the matrix values have data bars applied using conditional formatting. This makes it easier to see where the high and low values are without needing to read the numbers.

Making any visual interactive using a nifty little trick I picked up here. In essence, you copy the visual and change whatever variable you want. In this case I changed the house to the cohort on the rows for the matrix. You then place them above one another. Now create two buttons. Open the selections pane and hide one of the visuals. Save a bookmark for that view and apply a bookmark action for the relevant button. Repeat for the second button but this time change the visibility of the visuals (hide one, make visible the other). Now, when you click the buttons it will look like the visuals on the variables are changing, but in reality you are just hiding one and showing the other.

Other design features

I decided to place the main slicers used on the main page rather than hiding them in the filter panel. It is essential that the reports are quick and easy to use and therefore filters as buttons to press at the top of the page will facilitate it’s useability. I’ve changed the setting of each slicer to use horizontal orientation which can be found in the general section of the formatting options.

Horizontal slicers are placed at the top of the page to make the report more user friendly.

Cards are used in the top left to show key metrics at a glance. Each of these are measures that were described earlier, but now they are just dragged onto the card visual rather than a graph or table. These are prominently placed and are thus what would be first seen upon opening the report.

Key metics are displayed using the cards visual and prominently placed in the top left of the report page.

The final chart on the page shows the average lateness on each day for the selected dates vs. a year prior to the selected dates. It helps us to understand how the punctuality of our pupils might be changing over time. Once again, these measures were already created and now how just been dropped onto an area chart along with the dates for the x-axis.

On average, how late to morning registration are our pupils. This shows trends over time and a comparison to the previous year.

Finally, to tie all the elements of the report together I’ve used a light green background for each area of the report (alongside darker green text, borders and grid lines). These are just shapes drawn onto the canvas on of which every other element is added. They help to keep the page organised for the user by connecting related elements.

There are other pages I’ve created in the report that I’ll share at a later date. But for now, I hope this gives a good idea of the process involved in creating such a report. It’s the end of term now, but when rolling this out fully next academic year I believe it will make a big difference in enabling us to easily focus our energy on improving punctuality and attendance for individual pupils.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s