Comparing measures year on year is normally a trivial task. Drop your years into the columns of a matrix and months into the rows and there you go. This all works so long as the dates in the years you’re comparing don’t “overlap”. How can they overlap? An example is the best way to illustrate this issue. Say we’re comparing student withdrawals between academic years which run SEP to AUG. But withdrawals from an academic year may be recorded with a date after the end the actual end of the academic year. So we might have:
What we’re actually dealing with here is the fact that an academic year is both a time period and dimension in its own right that can be considered independently of the period it covers.
If we create a matrix visual with columns of academic year and rows for months it won’t distinguish between, for example, the September during the academic year and the September after it. There’s a number of ways to deal with this, my preferred solution is to create a new calendar table that has an extended range of dates for each academic year that will cover all the fact dates and add an additional column to distinguish between months in, before and after the academic year. I’ll generate this in SQL based on existing calendar and academic year tables. Assuming a very simple calendar and academic year table:
create table academic_years ( academic_year_id int , academic_year_name varchar(10) , begin_year int ) create table calendar ( date datetime , academic_year_id int , academic_year_month int )
Each academic year has a name and the calendar year it starts in – eg: name 2021/22 begin year 2021. Each date in the calendar has an academic year id so we know which academic year it belongs to. Below I’ll populate these with three academic years and the calendar with all the dates for these three years (the calendar query relies on the existence of an integers table).
insert into academic_years values (1, '2018/19' , '2018') insert into academic_years values (2, '2019/20' , '2019') insert into academic_years values (3, '2020/21' , '2020') insert into calendar select DATEADD(day, i.number - 1, '01-SEP-2018') calendar_date , case when DATEADD(day, i.number - 1, '01-SEP-2018') <= '31-AUG-2019' then 1 when DATEADD(day, i.number - 1, '01-SEP-2018') <= '31-AUG-2020' then 2 when DATEADD(day, i.number - 1, '01-SEP-2018') <= '31-AUG-2021' then 3 end academic_year_id , (((month(DATEADD(day, i.number - 1, '01-SEP-2018'))) + 3) % 12) + 1 academic_year_month from integers i where i.number <= DATEDIFF(day, '01-SEP-2018', '01-SEP-2021')
Now I’ll generate a new calendar that includes dates for each academic year a year before and after the actual date of the year. Using the following join will return, per academic year, the dates in that year, and the dates in the prior and next academic years:
from academic_years a join academic_years a1 on a.begin_year = a1.begin_year or a.begin_year = a1.begin_year + 1 or a.begin_year = a1.begin_year - 1 join calendar c on a1.academic_year_id = c.academic_year_id
In this extended academic year calendar I’ll include columns for academic year id, academic year name, a year prefix which will show whether a date is in the academic year, before or after it, the month name, the month number of the data in the academic year and the date. Since the date is no longer a primary key I also need a new field year_data_key for a unique key.
create view calendar_long_years as select a.academic_year_id , a.academic_year_name , case when a.begin_year > a1.begin_year then '1 Before AY' when a.begin_year = a1.begin_year then '2 During AY' else '3 After AY' end year_prefix , datename(month, c.date) month_name , c.academic_year_month , c.date , cast(a.academic_year_id as varchar) + '_' + CONVERT(varchar, c.Date, 112) year_date_key from academic_years a join academic_years a1 on a.begin_year = a1.begin_year or a.begin_year = a1.begin_year + 1 or a.begin_year = a1.begin_year - 1 join calendar c on a1.academic_year_id = c.academic_year_id
If I add this calendar to my Power BI data model I can join to it from any fact table which has an academic year key and a date dimension key on a composite year_date_key.
If the dates can extend beyond the range of the academic year I can distinguish between months during, before or after the academic year by adding the year_prefix column – setting up the visual like this.