Best practice for date table(s) - education time periods

[bretts]

Hi all,

In general, what is the best practice for configuring a date table to handle a university-type scenario?  For example, we'll have a term, say Fall 2021.  There are three primary time periods associated with this term:

Fall 2021 Application cycle - runs from say 11/1/2020 to 9/1/2021

Fall 2021 Enrollment cycle - runs from 4/1/2021 to 9/1/2021

Fall 2021 Term cycle (actual time period of the semester from start to end) - 9/1/2021 to 12/20/2021

Terms are denoted by an ID number, so I have a lookup table with the ID and term description.  I initially started my project by adding a Term ID field to my calendar table for the enrollment time period only (so Fall 2021 = 2150, dates from 9/1/ to 12/20 have 2150 in this custom column).  For the most part that does some of what I want to do, but only for that particular period.  Should I have a separate table for each time period that relates to a standard calendar table?  Should all time periods be denoted within the single calendar table?  A little stuck at this point.

Ideally I'd like to be able to compare application/enrollment numbers across terms (say Fall '21 compared with Fall '20 during the same application/enrollment cycle for that term).  Also, start/end dates may not always fall on the same date each year, so I imagine I'd want to compare for example Fall 21 application period to Fall 20 application period.

Any guidance appreciated!

**ADDED:

I should also mention that time periods overlap at certain points.  For example, on 4/15/2021 the following would be true:

Fall 2021 registration period open

Fall 2021 application period open

Spring 2021 term in progress

Spring 2022 application period open

[v-stephen-msft]

Hi @bretts ,

What expected results do you want to display? Currently, I can only recommend that you refer to this article to create a calendar.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bretts]

I have a calendar table built, but basically what I want is to have some visuals on a page with a 'period' slicer.  So for application data... if someone picks Fall 2021 it displays related info for dates 8/1/2020 to 9/1/2021 because that is the application period for that term.  If they also pick Fall 2020 on the slicer it should display Fall '21 info plus Fall '20 info (dates 8/1/2019 to 9/1/2020).  As you can see those two periods overlap which is what is confusing me.

Do I need boolean columns in the calendar table to denote which period belongs to each date?  i.e. 'isSpringTerm', 'isFallTerm', etc. so a single date, for example 8/15/2020 would have 'true' for both columns?

Terms are identified in increments of 10 starting with 2060, so was trying to figure out a way to work MOD into a calculated column to identify fall, spring, and summer terms:

2150 = fall '21.  MOD(2150/30) = 20.  Any term that this mod function equals 20 is a fall term

2140 = summer '21.  MOD(2140/30) = 10.  Any term that this mod function equals 10 is a summer term

2130 = spring '21. MOD(2130/30) = 0.  

2120 = fall '20.  Mod returns 20 on 2120/30 (fall term)

and so on.

[v-stephen-msft · Accepted solution]

Hi @bretts ,

You can use the calendar to create a separate calculated table, and the fields inside will be put into the slicer.

Here's the workaround.

1.Make sure the calendar has the year column

Year = YEAR([Date])

2.Create the calculated table

Calculated table = ADDCOLUMNS(DISTINCT('Calendar'[Year]),"Period",[Year]&" Fall")

3.Create the following measure and put it into Filters. Set as follows.

Measure = 
var _min=MIN('Calculated table'[Year])
var _max=MAX('Calculated table'[Year])
return IF(MAX('Calendar'[Date])>= DATE(_min-1,8,1) && MAX('Calendar'[Date])<=DATE(_max,8,31),1,0)

If you want to compare, just hold down Ctrl and select multiple.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@bretts So basically you need a custom date table. You can create this in DAX or Power Query. In DAX, start with the CALENDAR function to create the table and then add columns to it to denote your custom intervals, "Fall", "Winter", etc. I created a custom 445 calendar in DAX so it may provide some guidance. https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Custom-445-Calendar/m-p/1388582#M627