Calculating YTD With Variable Start And End Dates Of The Year (Academic Calendar)

[intapiuser]

Question: For a specific industry, like Education, even though the number of days is roughly the same every year, each academic year can start and end on different dates. The usual way of calculating YTD wouldn't work for this requirements because school year can start and end in the middle of calendar year.

Solution: The idea is we want to compare the years by the numbers of days that have passed since the current year started, e.g. compare the first 135 days of this school year with the first 135 days of last school year, the first 135 days of last two school years, the first 135 days of last three school years, and so on, regardless of the actual dates.

To accomplish this, we need to create a date dimension table with a day rank and a YTD flag. Day rank is an enumeration of school days within each school year. By comparing the day rank of each day by the day rank of today's date, we can calculate the YTD flag.

We need these two tables for this solution to work:

• List of school year with start and end dates
• List of dates. You can use the date dimension Excel file from this article.

School Year table:

Date table (you only need the Date column for this purpose)

Step 1

Create a new dim date table using the custom table option:

• Get the list of school dates (only dates between start and end dates of each school year) and remove dates that fall on Saturday and Sunday (optional).
• Rank the dates within each school year.
• Based on the result from above, get today's date rank.
• Compare each date's rank with today's rank to calculate the YTD flag.

You can use this SQL as an example.

--Join the list of school dates with today's date rank
--Compare each date's rank with today's date rank to create YTD flag
SELECT *,
CASE WHEN t1.[Day Rank] <= t2.[Today Day Rank] THEN 1
ELSE 0
END AS isYTD
FROM
 (
 --Get the list of only school dates (dates between start and end date)
 --Remove dates that fall on Saturday and Sunday (not school dates)
 --Rank dates within each school year
 SELECT d.Date, s.[School Year], s.[Start Date], s.[End Date], rankasc(s.[School Year], d.Date) AS [Day Rank]
 FROM [Dim Dates] d
 JOIN [School Year] s
 ON d.Date BETWEEN s.[Start Date] AND s.[End Date]
 WHERE DayOfWeek(d.Date) <= 5
 ) t1

JOIN

 (
 --Get today's date rank
 SELECT t.[Day Rank] AS [Today Day Rank]
 FROM
  (
  --Get the list of only school dates (dates between start and end dates of each year)
  --Remove dates that fall on Saturday and Sunday (not school dates)
  --Rank dates within each school year
  SELECT d.Date, s.[School Year], s.[Start Date], s.[End Date], rankasc(s.[School Year], d.Date) AS [Day Rank]
  FROM [Dim Dates] d
  JOIN [School Year] s
  ON d.Date BETWEEN s.[Start Date] AND s.[End Date]
  WHERE DayOfWeek(d.Date) <= 5
  ) t
 WHERE t.Date = createdate(getyear(now()), getmonth(now()), getday(now()))
 ) t2

ON 1=1

The table should look like this:

Step 2

Link the fact table(s) to the new date dimension table on the Date field.

Step 3

Create your widget(s) and add the YTD flag as a filter, set to 1.