Attendance Trends
Track pupil attendance by academic year, term, month, week and even day-by-day.
Reasons for Absence
During this course we will create this example page, which allows you to analyse why a pupil has been absent from school.
KS2 Prior Attainment Venn
Another real-life example of a visualization that we will create on this course.
Who is this course designed for?
This course is for data managers and aspiring data managers. The people who take this course will include data managers themselves, SIMS managers, senior leaders, exams officers, adminstrators etc. Broadly, it's suitable for anyone who wants to understand Power BI using pupil data.
What prerequisites are there for taking this course?
If you have completed the Power BI Skills course and want to continue to learn more skills, this is the course for you. Alternatively if you have been using Power BI for a few months and you can do the basics this course will enable you to progress on to the more complex areas of Power BI knowledge.
What you will get out of this course
By the end of this course you will be a confident user of Power BI with your school's data. You will understand how to use all the main areas of Power BI (Power Query Editor, Power BI Desktop and the Power BI Service) and you will have a working knowledge of DAX, the computer language that underpins Power BI.
Extra free course: Using the SIMS KS4 Resources
To help you visualize A8 and P8 data using SIMS, this course also includes free access to the KS4 Resources course.
Format of the course
This course is presented in a weekly format, with a weekly topic and set of lectures relating to that topic. You also have access to the webinars recorded during earlier live sessions.
Twenty+ Hours of Video Content
At the last count, there were over 20 hours of relevant, in-depth content in this course.
Pay via invoice or card
You can pay by card, purchase order or I can send you an invoice. If you are paying by purchase order or invoice, send an email to [email protected]. If you want me to send an invoice, please also let me know the email address to send the invoice to (usually your finance team).
Certification
At the end of the course you will be able to claim a certificate of completion.
Content of the Course
See below
How long will the course last?
You should expect the course to take between 1 and 3 months to complete. But it is possible to complete the course in a couple of weeks.
Course Guarantee
Not what you want? Email me within 30 days of enrolling and I will arrange a full refund.
Note for 2022 onwards
This course was recorded in 2020. Since then there have been some minor changes to the menu structures in Power BI. These changes are so minor that they are unlikely to impair the usefulness of the course. If in doubt, contact us.
Questions?
Email me at [email protected]
Curriculum
- 1.00 Before we start - deselect auto date and auto relationships (2:42)
- 1.01 What is DAX? (2:23)
- 1.02 We've already used DAX before (1:37)
- 1.03 Setting up a new report file - without the auto relationships (5:09)
- 1.04 Create a measure to count pupils (7:36)
- 1.05 Two measures to show the earliest and latest birthday (6:29)
- 1.06 Sum and average minutes late (9:38)
- 1.07 Understanding measures using a simple table - and introducing 'context' (10:09)
- 1.08 Creating a measures table help organise your measures (4:45)
- 1.09 Using a calculated column to calculate percentage attendance (7:18)
- 1.10 Using a calculated column for persistent absentees (3:43)
- 1.11 A tip for working with measures and calculated columns (7:45)
- 1.12 Why measures are essential to calculate average percentage attendance (5:34)
- 1.13 Calculated column or measure? A guide. (3:09)
- 1.14 Counting boys and girls (8:11)
- 1.15 Pupils in multiple categories (5:15)
- Appendix 1: Resources for this section of the course
- Week 1 webinar recording (48:04)
- 2.01 Importing DFE performance data as an excel file (5:42)
- 2.02 Using OneDrive for Business to host your data files (6:52)
- Extracting data from tables on webpages (2:55)
- Extract pupil data from SIMS (11:28)
- Save your SIMS report for next time (2:17)
- Cleaning up pupil data in the Power Query Editor (16:56)
- Merging two columns together (forename and surname) (3:23)
- Extracting pupil photographs from SIMS (5:56)
- Importing and using pupil photos in a pupil dashboard (10:09)
- Adding tooltips to a table or chart (8:51)
- Creating a year group index column (7:58)
- Using Bromcom with Power BI (3:08)
- Adding a column to the SIMS extract report (5:56)
- Tip: adding a column to your original CSV file (5:56)
- Duplicating tables and merging two tables together (14:14)
- Using power query to anonymise pupil names (15:34)
- Live webinar for week 2 (30:16)
- Appendix 2: Resources for this section of the course
- About this part of the course
- Before we start - tidying up - deleting columns and applied steps in the Power Query Editor (11:37)
- Using DAX to create a calculated column for a pupil's age in years (3:42)
- Using DAX to calculate a 'years in school' column (2:03)
- Revision - calculated columns for percentage attendance and persitent absentees YTD (6:18)
- Revision - Count pupils using COUNTROWS and create a measures table (5:25)
- Revision - more COUNTROWS measures using FILTER and IN (7:26)
- Ethnicity measure and a tip for formatting your DAX code (6:58)
- Revision - Calculating percentages using measures (5:58)
- Calculating persistent absentees and percentage absence measures (8:12)
- Page one of your new report (part 1) (12:03)
- Page one of your new report (part 2) (9:48)
- Using Drill Downs to analyse within groups of pupils (9:37)
- Using tooltips to add context to graphs (7:54)
- Creating groups to band together year groups into key stages - and more (9:38)
- Using bookmarks and buttons to allow users to reset the report (8:29)
- Appendix 1: Extracting attendance year to date statistics from SIMS (17:24)
- Appendix 2: Webinar 12th June 2020 - and how to set up a 'drill through' (38:20)
- Appendix 3: pbix file
- Introduction to attendance marks analysis (1:33)
- Extracting attendance marks from SIMS part 1 (11:44)
- Extracting attendace marks from SIMS part 2 (3:51)
- Loading attendance marks by appending three tables together (9:29)
- Using an excel file as a lookup table (9:01)
- Inspecting our attendance data (7:40)
- Cleaning blank, null or error values from the csv file using Power Query Editor (7:20)
- Filtering out school leavers from attendance marks (2:23)
- New: Filtering out attendance marks for today or in the future (6:22)
- Create the percentage attendance measures (20:02)
- Power BI and dates - an introduction to date tables (10:25)
- Tips for attendance graphs (3:18)
- Create a date table using the CALENDAR function (6:43)
- Calculating the academic year and adding it to the date table (15:29)
- Calculating the term from the date (6:02)
- New: Calculating the term - accurately and allowing for Easter
- Adding 'academic year and term' and 'academic and week number' columns to the date table (5:36)
- Setting up a date hierarchy (3:35)
- Calculating the percentage absence rate (5:22)
- Introducing calculate (6:57)
- Rolling 30-day attendance - method 1 (10:04)
- Rolling 30-day attendance - method 2 (4:03)
- Calculating the 30 day percentage attendance for a pupil - using a calculated column (6:49)
- Understanding context using MAX(date) - and introducing the SAMEPERIODLASTYEAR function (11:45)
- New: Using a measure to apply conditional formatting 'traffic lights' (9:43)
- Bringing it all together...create an attendance page (13:24)
- New: An alternative way to count the possible sessions for dual registered pupils (5:26)
- New: Identifying persistent absentees over variable timesclaes (eg this week, last month etc) (9:12)
- Appendix 1: Attendance marks csv files
- Appendix 2: Attendance mark descriptions excel file
- Appendix 3: Webinar June 19th at 10am (59:50)
- Appendix 4: pbix file
- Introduction (1:02)
- Aspect naming conventions for SIMS assessment manager (3:46)
- Three methods for extracting assessment data (1:46)
- Method 1 - extract all the results into one big file (9:15)
- Method 2 - a report for each subject with links to classes (13:30)
- Method 3 - export classes and merge with assessment results (22:23)
- Importing assessment data into power query editor (10:37)
- Extracting information from the aspect name to create new columns we can filter (10:01)
- More about relationships (it's complicated!) and a hierarchical slicer (7:53)
- Calculate an average grade measure (3:41)
- A measure to count grades (2:43)
- Measure to calculate how many pupils have a grade 5 or higher (5:42)
- Calculate the percentage of pupils at grade 5 or higher (4:16)
- Remember - we aren't just looking at attainment grades (2:00)
- Average grades for PP, Gender and SEN (6:58)
- Extracting KS2 prior attainment from SIMS (9:16)
- Pivoting the KS2 prior attainment data to create 4 new columns (7:07)
- Create a KS2 low-medium-high prior attainment column (5:48)
- Comparing target and actual grades to calculate a residual (12:54)
- Free access code for the KS4 Resources course
- Using the KS4 Resources with Power BI (4:52)
- Exporting A8 and P8 data from SIMS (7:04)
- Using the SWITCH command to convert grades to marks (and marks to grades) (7:27)
- Appendix 1: pbix file
- Appendix 2: csv source files
- Appendix 3: Webinar (75:14)
- 6.01 Including leavers in our pupil data - part 1 extracting from SIMS (3:33)
- 6.02 Dealing with leavers and on roll pupils within Power BI (7:59)
- 6.03 Extracting behaviour data from SIMS (8:38)
- 6.04 Exporting achievement from SIMS (4:11)
- 6.05 Exclusions data from SIMS (4:29)
- 6.06 Cleaning up the behaviour data using power query editor (8:57)
- 6.07 Understand your data by using PQE to inspect column quality, column profile and column distribu (4:41)
- 6.08 Measures to sum, average and count behaviour points and incidents (5:16)
- 6.09 Create a date table for behaviour dates (9:45)
- 6.10 Using PQE to create a 'behaviour category' column - especially useful for MATs (8:33)
- 6.11 Live webinar - reducing the size of the attendance marks file plus understanding relationships (42:26)
- 6.12 Rolling and static averages for behaviour (5:59)
- 6.13 Achievements. acheivement measures and how to use data model layouts (15:04)
- 6.14 Exclusions (11:50)
- 6.15 Decomposition trees (8:27)
- 6.16 Creating a venn diagram for KS2 results using an imported visualization (17:30)
- Appendix 1 - csv files
- 7.00 Introduction - the 'big picture' for power bi (8:46)
- 7.01 Automating our csv files part 1 (4:44)
- 7.02 Automating our csv files part 2 (19:48)
- 7.03 Automating our csv files part 3 (12:28)
- 7.04 Dealing with errors when we refresh our data (3:59)
- 7.07 Sharing reports with colleagues using the pro license (4:16)
- 7.05 Viewing and presenting using the Power BI Service (7:30)
- 7.08 Subscribing to reports to receive email updates (4:07)
- 7.06 Power BI service - export to powerpoint and pdf (3:26)
- 7.07 Live webinar - automating and sharing (70:36)
- 8.01 Tidying up - and a few tips for organising your report (8:43)
- 8.02 Using, finding and creating Power BI themes (6:06)
- 8.03 Creating your own theme to match the school logo colours (7:24)
- 8.04 Finding background images and using fonts (5:41)
- 8.05 Using powerpoint to create a simple page background with logo (1:49)
- 8.06 Formatting your report pages using grids (3:16)
- 8.07 Adding buttons to navigate to specific pages and create a table of contents (5:43)
- 8.08 Using icons in Power BI and adding icons to buttons (6:18)
- 8.09 Linking buttons to bookmarks (10:10)
- 8.10 Styling the whole school summary page (9:20)
- 8.11 Setting up the filter pane for our teachers (4:59)
- 8.12 Adding a 'pupil demographics ' page (6:37)
- 8.14 Aligning and grouping visualizations for neatness (5:17)
- 8.13 Create an SEN YesNO column and visualize the data in a donut chart (5:38)
- 8.15 A venn diagram to identify pupils with multiple vulnerabilities (17:04)
- 8.16 DAX revision - academic week and academic month numbers (11:12)
- 8.17 Live webinar - attendance trends and absence reasons (61:45)
- 8.17 Fine tuning and duplicating attendance pages to develop behaviour graphs (9:53)
- 9.01 Introduction to assessment data analysis (7:26)
- 9.02 Linking national statistics to the assessment tables using an Excel spreadsheet (11:24)
- 9.03 Creating a KS4 results table including residuals and comparisons to national averages (18:58)
- 9.04 Live webinar - creating trend and class analysis pages for individual subjects (68:58)
Get attendance percentages by day, week, month, term, academic year or three year average...
View daily attendance percentages, then aggregate the daily percentages up to weekly, monthly or termly attendance. View as a bar chart, pie chart, simple percentage or line graph over time. Filter by pupil premium, EAL or SEN. Compare with national average. All at the press of a button.
Ribbon Charts for Absence Reasons
View absence trends over a period of time using this ribbon chart. You can select to look at trends over an entire academic year, month, term, week or just one day. The great thing about these graphs is that they rank and order the data, so you know which types cause the most absences and also the trend over time. So you'll know if medical appointments are a factor, and also you will see the trend over the last year, term, week or day. We create this ribbon chart during the course.
About Me
I'm an independent SIMS and Power BI consultant working with primarily with SIMS for nearly 10 years. I spend lots of time in different schools, talking about the best ways to use SIMS and Power BI and creating effective assessment and analysis systems. I run a popular blog full of tips and tricks for data managers in schools. You can email me at [email protected].
Kind regards,
David.
Feedback
The course was brilliant, you explain everything so well - Karen Green.
Brilliant! David is so knowledgeable on everything SIMS and PowerBi, the training was clear, paced perfectly and gave me all the skills I needed to get started! - Samantha Barnes
I truly enjoyed the course, thank you so much. I've spent more time with you than my wife (so she says!). I hope that I can set up analysis now for our trust. - Richard Horswell