This guide shows you how to create a dynamic filter in Power BI using your Bromcom MIS data. In this guide we focus on Attendance data in the the AttendanceSummaryByWeek table.

Pre requisites:

• AttendanceSummarybyWeek table loaded into Power BI (please see this article for guidance on how to load data into Power BI), and
• some basic familiarity with power BI.

Create a Dynamic Field for this years’ data

First, apply a filter to the table; in this instance, the current academic year.

This will then show in the Applied Steps.

To create the dynamic filter, click on the Advanced Editor on the top ribbon, making sure the correct table is selected.

You will see that #“Filtered Rows” has been added to the M code. To make this filter dynamic we need to create our own custom year filters to replace the hard-coded years (2020,2021).

Copy and paste the following custom filter code into the Advanced Editor, as showed in the screenshot, and replace the hard-coded years. Make sure to replace “2020” with “CustomACYear_Start”, and “2021” with “CustomACYear_End”. Then click Done.

CurrentACYear_Start = if Date.Month(DateTime.LocalNow()) < 9 then Date.Year(DateTime.LocalNow()) – 1 else Date.Year(DateTime.LocalNow()), CurrentACYear_End = CurrentACYear_Start + 1,

At this point the table is now filtered and ready to be loaded into the model (by clicking Close & Apply).

Add Previous Years

To add previous years – in this instance, two years – repeat the above steps to ensure you have three copies of the table. Rename the new tables as per the screenshot.

Click on the Advanced Editor (again, making sure the table in question is selected) and amend the M codes, respectively, for each table.

Make sure to replace “2020” with “CustomACYear_Start”, and “2021” with “CustomACYear_End”. Then click Done.

For AttendanceSummaryByWeek_MinusOne

CustomACYear_Start = if Date.Month(DateTime.LocalNow()) < 9 then Date.Year(DateTime.LocalNow()) – 2 else Date.Year(DateTime.LocalNow()) – 1, CustomACYear_End = CustomACYear_Start + 1,

For AttendanceSummaryByWeek_MinusTwo

CustomACYear_Start = if Date.Month(DateTime.LocalNow()) < 9 then Date.Year(DateTime.LocalNow()) – 3 else Date.Year(DateTime.LocalNow()) – 2, CustomACYear_End = CustomACYear_Start + 1,

Under Queries right click on MinusOne and MinusTwo tables and untick Enable Load. Once done, the tables will appear italic. These are now disabled and will not load into the model, as they are historical (static) data for comparison purposes.

Make sure the original table is selected (AttendanceSummaryByWeek) and click Append Queries on the top ribbon.

Choose Three or more tables and add the additional MinusOne and MinusTwo tables. Then click OK.

Appended Query should have been added to Applied Steps under Query Settings. The filtered & appended table is now ready to be loaded into the model.

Click Close & Apply and wait for the table to load.