School term week number


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:

My term dates table looks like this:

Any help to get me started would be greatly appreciated.
Kind regards, Dale

Hi @dalef,

Welcome to the Forum!

I’d be happy to look into that with you so could you supply the term dates table data and confirm are you referring to this basic date table?


1 Like

Hi Melissa,
Yes, that’s the table I’m using.
Regards, Dale
Sorry, here’s the term dates file.
terms.csv (443 Bytes)

Hi @dalef,

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 …?

Let me know.

They could be called “Holidays” if that is possible.
Thanks, Dale

1 Like

Hi @dalef,

Thanks for the quick reply! :+1:

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.

No problem Melissa, a 0 would be fine too.

Hi @dalef,

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 =>
    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)

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


You will find both methods and some other supporting queries in the attached PBIX

eDNA - Basic Date Table with Terms.pbix (134.7 KB)

I hope this is helpful


That is going to be so helpful Melissa. I’m sure a few other school admins will make use of this. Thank you so much.
Regards, Dale

Hi @dalef, welcome to the Forum! :slight_smile:

It’s great to know that you are having a great experience so far and that your inquiry was answered immediately.

Please take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum.

Also, if your question was answered, kindly mark the response that helped you solved your inquiry as SOLUTION. :slight_smile: