Schools count their weeks from the beginning of terms. I’d like to add a week number to the basic date table that resets at the start of each term. I’m looking for something like this:
Quick question since the date ranges are non-contiguous between Terms and/or Semesters, so what value should be given to a date that doesn’t fall between the Start- and End Date?
A null or …?
Just to note that would mean a data type change from integer to text string (not optimal). Alternatively we could assign those days a 0 (zero) to indicate these are holidays. Would that be acceptable? If not that’s no problem.
So this is what I’ve got for you.
Created a separate fxCreateTermTable function
( Semester as number, Term as number, StartDate as date, EndDate as date ) as table =>
let
DayCount = Duration.Days( EndDate-StartDate )+1,
DateList = List.Dates( StartDate, DayCount, Duration.From(1) ),
AddPeriodName = List.Transform( DateList, each { Semester, Term, _ } ),
CreateTable = #table( type table[Semester=number, Term=number, Date=date], AddPeriodName),
AddWeek = Table.AddColumn(CreateTable, "Term Week", each ((Number.From( Date.StartOfWeek([Date], 1)) - Number.From(Date.StartOfWeek(CreateTable[Date]{0}, 1))) /7) +1, type number)
in
AddWeek
When invoked this returns a TermsTable support query.
Then you could merge this with the Dates table and add a “Replace values” step to swap out the nulls with 0’s.
Alternatively I’ve created a fxCalendarTerms function that does this for you BUT that requires a table called: Term which needs to include these field (column) names AND requires these data types.
Else you will have to modify the code highlighted here and/or convert datetime values into date datatypes by wrapping them in Date.From