Calendar Dimension

By Dani

March 11, 2023

Calendar tables are possibly my favorite topic in the world of data modeling. I am obsessed with the pursuit of the perfect calendar. Perhaps this is because time is such a powerful and sometimes difficult dimension to master. Just when you think you have it all figured out, some intrepid user throws you a curve ball like deciding they need reporting with all Tuesdays aligned.

One thing I know for certain is a good calendar table can solve a lot of problems, and it will be much easier for members of a team to work on each others reports if everyone uses the same calendar and follows some standard practices. If you’ve supported a body of BI content for very long, you will be familiar with the problems that can arise from mishandling dates. Let’s list a few of these time bombs:

Models that have to be touched at each year end.
Hard-coded dates several months later than publication and two weeks after the author leaves the company.
Static start dates with no end date for ever-growing models.
Year-over-year reports that don’t give users a chance to see year-over-year for year-end because they dump everything prior to current - 1 year on the first day of the year.
Models with multiple uncoordinated date-bounded tables.

I could go on and on. Frustration over time bombs started me on this journey years ago. I hope what I have learned helps you avoid or overcome time bombs.

Pivot Calendars

The calendar table I will cover here is completely generated with Power Query. In the future, I will have an article about generating a calendar table for a database. The basics of this table are the same, but an in-report calendar table should be more than just a static table of date rows. I refer to my report calendars as Pivot Calendars because they include some columns that are relative to the “as of” date of the report. Not surprisingly, I refer to that “as of” date as the Pivot Date.

Pivot Date

Just the concept of a Pivot Date and some calendar columns relative to that solves a ton of problems. Some reports are as of the current date. These are usually Direct Query reports or Imported data that refreshes multiple times a day. Many reports refresh once daily and provide data as of the close of business on the prior day. You’ll also sometimes have archival reports that include a few years in the past to supplement the current versions of the reports that don’t include all those past years. If developers are in the habit of writing formulas using DAX’s TODAY() or Power Query’s DateTime.LocalNow(), that logic will break when the report is as of year end five years ago. What about reports that only get new data for five business days a week? Have you ever gotten a support request claiming a report was broken from someone viewing your report on a Sunday, and the only problem was the report was filtered with the built-in Relative Date filter for Last 1 Day? All these scenarios are handled when developers write logic to a Pivot Date.

Pivot Columns

These are the most handy columns I use in a Pivot Calendar that are relative to the Pivot Date:

topn_days - an integer that reads like Days Before Pivot Date. Pivot Date = 0, day prior to Pivot Date = 1 and day after Pivot Date is -1. If you’ve ever written a model with future dates included to handle projections, determining where actuals stop and projections need to start is as simple as checking topn_days > 0.
topn_weeks, topn_months, topn_quarters, topn_years - same logic as topn_days but for weeks, months, quarters, and years. If you work off a non-standard fiscal calendar like a 445 calendar, these are lifesavers. DAX’s Time Intelligence functions are almost useless with 445 calendars. These topn columns provide a lot of the same functionality.
topn_yoy_days, topn_yoy_weeks, topn_yoy_months, topn_yoy_quarters - this is a yearly version of the topn_ columns. In each year, the date that corresponds to the Pivot Date has a topn_yoy_days value of 0. Days prior are positive numbers indicating days prior to YoY Pivot Date. Days after are negative numbers indicating days after YoY Pivot Date. Different organizations will determine this date differently. If you’ve never thought about it before, you’ll think this is a silly question and assume that if the Pivot Date is March 13 of the current year, every March 13 in every other year should be the YoY Pivot Date. However, some organizations will say the YoY Pivot Date should be the date that is the same number of days from the beginning of the year. An organization using a 445 calendar will probably define it as the same week of year and day of week. So if the Pivot Date is the 3rd Thursday of the 28th week of the year, all YoY Pivot Dates are the 3rd Thursday of the 28th week of the year.
is_wtd, is_mtd, is_qtd, is_ytd - true/false indicating whether the date is period-to-date. Unlike DAX’s implementation of To-Date logic, these can be False in prior years. The YoY Pivot Date will drive the exact implementation. For is_ytd, dates less than or equal to YoY Pivot Date are true, and dates after are false.

Using the Pivot Calendar

I implement my calendar dimension as a Dataflow. This allows for changing fiscal logic and having it flow through all reports. This is primarily useful with the is_wtd etc columns. If the organization decides to redefine how that’s handled, you won’t need to touch every report. The dataflow also means if you’re pulling any supplemental data from a database such as company holidays, that is only pulled once daily for all reports instead of for every report refresh. I keep two or three instances of the calendar dimension in the dataflow. One is pivot_cal_today which has a pivot date of the current date. The other is pivot_cal_prior_day which has a pivot date of yesterday. Another option some organizations will need is pivot_cal_last_business_day at a company that was strictly weekday-only business. This calendar’s pivot date was the most recently completed business day, so on Saturday, Sunday, and Monday, pivot date was Friday. On other days, it was the prior day. For all three of these instances of the Pivot Calendar, I include 7 prior and 7 future years for a total of 15 years. This is usually overkill, but it’s pretty cheap resource-wise and gives developers flexibility without implementing their own calendar dimension. Definitely encourage developers to filter the calendar in report for just the dates they need.

Smart Date Controls Preview

An upcoming article will cover a set of Power Query parameters and queries I use in every report that make it very simple to set up a date range for reporting that can be used to filter all queries. This means you change one or two parameters and all queries in the model update, and that includes submitting those dates in native SQL queries to pull back exactly the dates you want. These Smart Date Controls include logic that does the following

Calculate the Pivot Date as n days before or after the current date.
Select the right dataflow depending on the Pivot Date offset days.
Calculate a Start Date as n months or years before the Pivot Date.
Adjust the Start Date to the beginning of a month or year.
Very similar controls for End Date if future dates are needed.
Hard-coded dates that override the relative calculations. This is needed for archive reports that should always be between a static range.
Apply alternate Start Date logic for N days after a new year. This is so that if you have a report that typically maintains current and prior year data, users don’t miss the chance to see year-over-year data for a just closed year when January 1 rolls around and all the prior, prior year data rolls off. This can save you from having to maintain 3 years of data in all your reports when users really just want to be able to see last year’s year-over-year data for a few weeks at the beginning of the year.

Most BI Developers I’ve ever spoken to about this kind of logic get pretty excited. It’s all straightforward to do but would not be feasible to maintain across an entire environment without a standardized calendar dimension. When everyone is handling dates the same way, that is a big hurdle developers don’t need to make to work on a teammate’s models.

Why Power Query

If Power Query will work for your calendar needs, use it instead of a database table. The reason is at some point, a model will need a calendar dimension to do something the standard team calendar doesn’t do. In those cases, you can use the standard calendar as a base right in the report and adjust it for requirements. It will still be as close as possible to what everyone is familiar with. Divergences are easily documented.

Why Not Power Query

This Power Query calendar does not handle complex fiscal calendar logic such as a 445 calendar. I have no doubt it could be done, but I’d opt for a database table in that situation. Another upcoming article will provide a script to generate a 445 calendar in both MS SQL and Postgres.

Another reason not to use a Power Query generated calendar is if you have a great calendar dimension in your data warehouse already that fits your needs. It might have organization-specific data like Holidays and other working / off day information. In that case, you might as well use the calendar table developers are probably using in their queries. It can be supplemented with the pivot column logic in Power BI. Especially if you implement it as a Dataflow, you’ll still only be pulling it daily instead of per report.

Finally, the Power Queries

This works in a series of 3 queries. In Dataflows, I set them up as a single query, but in a report, I keep them separate. I present them separately here because it’s easier to follow what’s going on.

Base Calendar aka cal_base

Not imported.
References parameters #“Start Date”, #“End Date”, and #“Pivot Date”. These are date type parameters which are part of the Smart Date Controls which will be covered in an upcoming article. You can set these parameters to any date value to start using these queries.

let
    start_date = #"Start Date",
    end_date = #"End Date",
    pivot_date = #"Pivot Date",
    Source = List.Dates(start_date, Duration.Days(end_date - start_date) + 1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"datekey"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"datekey", type date}}),
    add_yr = Table.AddColumn(#"Changed Type", "cal_year", each Date.Year([datekey]), Int64.Type),
    add_mo = Table.AddColumn(add_yr, "cal_month", each Date.Month([datekey]), Int64.Type),
    add_dy = Table.AddColumn(add_mo, "cal_day", each Date.Day([datekey]), Int64.Type),
    add_qt = Table.AddColumn(add_dy, "cal_qtr", each Date.QuarterOfYear([datekey]), Int64.Type),
    add_wk = Table.AddColumn(add_qt, "cal_week_of_year", each Date.WeekOfYear([datekey]), Int64.Type),
    add_moq = Table.AddColumn(add_wk, "cal_month_of_quarter", each Number.Mod([cal_month] - 1, 3) + 1, Int64.Type),
    add_mnl = Table.AddColumn(add_moq, "cal_month_name_long", each Date.MonthName([datekey]), type text),
    add_mns = Table.AddColumn(add_mnl, "cal_month_name_short", each Text.Start([cal_month_name_long],3), type text),
    add_doy = Table.AddColumn(add_mns, "cal_day_of_year", each Date.DayOfYear([datekey]), Int64.Type),
    add_dow = Table.AddColumn(add_doy, "cal_day_of_week", each Date.DayOfWeek([datekey]), Int64.Type),
    add_dnl = Table.AddColumn(add_dow, "cal_day_name_long", each Date.DayOfWeekName([datekey]), type text),
    add_dns = Table.AddColumn(add_dnl, "cal_day_name_short", each Text.Start([cal_day_name_long],3), type text),
    add_date_int = Table.AddColumn(add_dns, "cal_date_int", each Number.FromText(Date.ToText([datekey], "yyyyMMdd")), Int64.Type),
    add_yyyyq = Table.AddColumn(add_date_int, "cal_yyyyq", each [cal_year] * 10 + [cal_qtr], Int64.Type),
    add_yyyymm = Table.AddColumn(add_yyyyq, "cal_yyyymm", each [cal_year] * 100 + [cal_month], Int64.Type),
    add_yyyyww = Table.AddColumn(add_yyyymm, "cal_yyyyww", each [cal_year] * 100 + [cal_week_of_year], Int64.Type),
    add_mo_start = Table.AddColumn(add_yyyyww, "cal_month_start_date", each Date.StartOfMonth([datekey]), Date.Type),
    add_mo_end = Table.AddColumn(add_mo_start, "cal_month_end_date", each Date.EndOfMonth([datekey]), Date.Type),
    add_week_start = Table.AddColumn(add_mo_end, "cal_week_start_date", each Date.StartOfWeek([datekey]), Date.Type),
    add_week_end = Table.AddColumn(add_week_start, "cal_week_end_date", each Date.EndOfWeek([datekey]), Date.Type),
    
    /* pivot date record */
    pvt = Table.SingleRow(Table.SelectRows(add_week_end, each ([datekey] = pivot_date))), 

    /* Begin adding pivot columns which are calculated relative to the pivot date */
    add_tnd = Table.AddColumn(add_week_end, "topn_days", each Duration.Days(Record.Field(pvt, "datekey") - [datekey]), Int64.Type),
    add_tndy = Table.AddColumn(add_tnd, "topn_days_yoy", each Duration.Days(Date.AddYears(Record.Field(pvt, "datekey"), Record.Field(pvt, "cal_year") - [cal_year]) - [datekey]), Int64.Type), 
    add_tny = Table.AddColumn(add_tndy, "topn_years", each Record.Field(pvt, "cal_year") - [cal_year], Int64.Type),
    add_tnq = Table.AddColumn(add_tny, "topn_quarters", each Record.Field(pvt, "cal_qtr") - [cal_qtr] + 4 * [topn_years], Int64.Type),
    add_tnqy = Table.AddColumn(add_tnq, "topn_quarters_yoy", each Record.Field(pvt, "cal_qtr") - [cal_qtr], Int64.Type),
    add_tnm = Table.AddColumn(add_tnqy, "topn_months", each Record.Field(pvt, "cal_month") - [cal_month] + 12 * [topn_years], Int64.Type),    
    add_tnmy = Table.AddColumn(add_tnm, "topn_months_yoy", each Record.Field(pvt, "cal_month") - [cal_month], Int64.Type),    
    add_ytd = Table.AddColumn(add_tnmy, "is_ytd", each if [datekey] + #duration(365 * [topn_years],0,0,0) <= Record.Field(pvt, "datekey") then 1 else 0, Int64.Type),
    add_qtd = Table.AddColumn(add_ytd, "is_qtd", each 
        if [cal_month_of_quarter] < Record.Field(pvt, "cal_month_of_quarter") then 1
            else if [cal_month_of_quarter] > Record.Field(pvt, "cal_month_of_quarter") then 0
            else if [cal_day] > Record.Field(pvt, "cal_day") then 0
            else 1       
        , Int64.Type),
    add_mtd = Table.AddColumn(add_qtd, "is_mtd", each if [cal_day] <= Record.Field(pvt, "cal_day") then 1 else 0, Int64.Type),
    add_wtd = Table.AddColumn(add_mtd, "is_wtd", each if [cal_day_of_week] <= Record.Field(pvt, "cal_day_of_week") then 1 else 0, Int64.Type)
in
    add_wtd

Weeks Calendar aka cal_weeks

Not imported

Weeks always throw a monkey wrench into the works because they don’t nest neatly into a standard calendar. If there’s anything you might want to tweak, it will probably be here. Do you want to count the partial first week of the year as week 1? How do you want to align weeks year-over-year? These are issues people have wrestled with so much that the 445 Fiscal calendar was born and is used by many businesses, especially retail businesses. This barely addresses all the issues that a 445 calendar does, but it can help if you’ve got that one intrepid business leader insisting to see reporting aligned weekly when everyone else is happy with months.

let
    Source = cal_base,
    pvt_row = Table.SingleRow(Table.SelectRows(Source, each [topn_days] = 0)),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"cal_year", "cal_week_of_year", "cal_yyyyww"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"cal_yyyyww"}),

    /* topn_weeks */
    add_tnw_yoy = Table.AddColumn(#"Removed Duplicates", "topn_weeks_yoy", each Record.Field(pvt_row, "cal_week_of_year") - [cal_week_of_year], Int64.Type),
    #"Sorted Rows" = Table.Sort(add_tnw_yoy,{{"cal_yyyyww", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),

    //get a pivot row with the newly-added index
    pvt_wk_row =Table.SingleRow(Table.SelectRows(#"Added Index", each [cal_yyyyww] = Record.Field(pvt_row, "cal_yyyyww"))),
    add_tnw = Table.AddColumn(#"Added Index", "topn_weeks", each [Index] - Record.Field(pvt_wk_row, "Index"), Int64.Type)
in
    add_tnw

cal

Imported!
Personal preference: I like to call my calendar “cal” instead of dim cal or anything more complex. It’s because I type it so frequently for DAX measures, I don’t even want to be bothered hitting tab to find it in autocomplete. I just want to type cal[ and go!

  let
      Source = Table.NestedJoin(cal_base, {"cal_yyyyww"}, cal_weeks, {"cal_yyyyww"}, "pq_cal_weeks", JoinKind.LeftOuter),
      #"Expanded pq_cal_weeks" = Table.ExpandTableColumn(Source, "pq_cal_weeks", {"topn_weeks_yoy", "topn_weeks"}, {"topn_weeks_yoy", "topn_weeks"}),
      #"Added Custom" = Table.AddColumn(#"Expanded pq_cal_weeks", "Report Month Filter", each 
              if [topn_months] = 0 then "Current Month"
              else if [topn_months] = 1 then "Prior Month"
              else Date.ToText([datekey], "MMMM yyyy"), 
          type text),
      #"Added Custom1" = Table.AddColumn(#"Added Custom", "Report Year Filter", each 
              if [topn_years] = 0 then "Current Year"
              else if [topn_years] = 1 then "Prior Year"
              else Text.From([cal_year]), 
          type text),
      #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Report Week Filter", each 
              if [topn_weeks] = 0 then "Current Week" 
              else if [topn_weeks] = 1 then "Prior Week" 
              else Text.From([cal_year]) & " Wk " & Text.From([cal_week_of_year]), 
          type text),
      #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Report Quarter Filter", each 
              if [topn_quarters] = 0 then "Current Quarter"
              else if [topn_quarters] = 1 then "Prior Quarter" 
              else Text.From([cal_year]) & " Q" & Text.From([cal_qtr]), 
          type text)
  in
      #"Added Custom3"