[Data Modeling] Three Ways to Create a Calendar Table

[ibarrau]

During data modeling, it is mandatory to create a calendar table. It is very necessary in different BI theories to create a calendar table with one day row by row, with needed columns like year, quarter, month, month name, day of week, day, etc. for good BI reporting for the end users. Nowadays, especially with Microsoft, the tabular model must contain a calendar table in order to accomplish great date calculations in DAX. I'm talking about Time Intelligence. To stop boring you with the importance of this, let's check where and how we can create one.

SQL Server

In any database engine we can create a calendar table. Since we probably use Microsoft on this forum, I'm showing the example in T-SQL. In order to create a calendar table, first create a store procedure that runs to build the calendar table at night or in each table processing of the tabular model.

Let's keep in mind that the SELECT statement only returns a table from a column with dates from and to what we have already written. Go on and read the complete script and then you can draw your own conclusions if you want to use the store procedure or just a query.

CREATE TABLE dim_date
(
 [date]       DATE PRIMARY KEY,
 [year]       AS DATEPART(YEAR,     [date]),
 [month]      AS DATEPART(MONTH,    [date]),
 [MonthName]  AS DATENAME(MONTH,    [date]),
 [day]        AS DATEPART(DAY,      [date]),  
 FirstOfMonth AS CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 0)),  
 FirstOfYear  AS CONVERT(DATE, DATEADD(YEAR,  DATEDIFF(YEAR,  0, [date]), 0)),
 [week]       AS DATEPART(WEEK,     [date]),
 [ISOweek]    AS DATEPART(ISO_WEEK, [date]),
 [DayOfWeek]  AS DATEPART(WEEKDAY,  [date]),
 [quarter]    AS DATEPART(QUARTER,  [date]),    
 [datekey]    AS CONVERT(CHAR(8),   [date], 112),
 [dmy]        AS CONVERT(CHAR(10),  [date], 101)
);

-- Procedure:

TRUNCATE TABLE dim_dateSET DATEFIRST 7;
SET DATEFORMAT dmy;
SET LANGUAGE Spanish;DECLARE @StartDate DATE = '20180101'
DECLARE @CutoffDate DATE = GETDATE()+1 INSERT dim_date([date])
SELECT d
FROM
(
 SELECT d = DATEADD(DAY, rn - 1, @StartDate)
 FROM
 (
   SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate))
     rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
   FROM sys.all_objects AS s1
   CROSS JOIN sys.all_objects AS s2
   -- on my system this would support > 5 million days
   ORDER BY s1.[object_id]
 ) AS x
) AS y;

Power Query

We won't always have the possibility of creating an SP in a database to create our table there, as we mentioned before. That is why we would have to continue our data modeling, adding the table in the previous step, before creating the final tabular model. The way to go is in Edit Queries or DataFlows. This might be the most used method by a consultant as a service. It is very attractive to have the table before change and apply queries. 

Be careful with the start date. It is manually written like the T-SQL query until end date (now). Get data from a blank query and paste this:

= let
//Capture the date range from the parameters
   StartDate = #date(2018, 1, 1),
   EndDate = DateTime.Date(DateTime.LocalNow()),
//Get the number of dates that will be required for the table
   GetDateCount = Duration.Days(EndDate - StartDate),
//Take the count of dates and turn it into a list of dates
   GetDateList = List.Dates(StartDate, GetDateCount,
   #duration(1,0,0,0)),
//Convert the list into a table
   DateListToTable = Table.FromList(GetDateList,
   Splitter.SplitByNothing(), {"Fecha"}, null, ExtraValues.Error),
//Add Year Column
   YearNumber = Table.AddColumn(DateListToTable, "Año",
   each Date.Year([Fecha]), Int32.Type),
//Add Month Number Column
   MonthNumber = Table.AddColumn(YearNumber , "IdMes",
   each Date.Month([Fecha]), Int32.Type),
//Add Month Name Column
   MonthName = Table.AddColumn(MonthNumber , "Mes",
   each Date.ToText([Fecha],"MMMM", "es-es"), type text),
//Add Day Number Column
   DayNumber  = Table.AddColumn(MonthName , "Dia",
   each Date.Day([Fecha]), Int32.Type)
in
   DayNumber

DAX

As I mentioned before, we might have less permissions in Power BI to build our model. Maybe, they send you a Power BI file without the data source connections and you can't access the edit queries. That is why we shouldn't leave behind the construction of the table, once the tabular model is already created. The advantage of this method is that it is easier to build the Calendar Table with the dates in the model and not necessarily manually defined like from last N years to today. I can easily create the table from minimum fact table date to maximum fact table date.

In the case of DAX, we can build the calendar from the fact or by manually adding with DATE() and NOW() functions, like before.

CalendarTable =
VAR MinYear = YEAR ( MIN ( FactTable[Fecha] ) )
VAR MaxYear = YEAR ( MAX ( FactTable[Fecha] ) )
RETURN
ADDCOLUMNS (
   FILTER (
       CALENDARAUTO( );
       AND ( YEAR ( [Date] ) >= MinYear; YEAR ( [Date] ) <= MaxYear )
   );
   "Year Number"; YEAR ( [Date] );
   "Month Name"; FORMAT ( [Date]; "mmmm" );
   "Month Number"; MONTH ( [Date] );
   "Day"; Day ( [Date] )
)

You may be wondering which one to use. For these kinds of situations, I have a phrase or speech: "The further back it is resolved, the better". If we have the change of building the calendar table in the data source because we have a warehouse or database, then do it there. Otherwise, I would try it on Power Query, leaving the DAX query as the last resource. We shouldn't build data models without a calendar table even if it is a small BI project.

What about you? Are you adding a calendar table to your data model?

My original Blog post

[taku]

Hi

I need to create an HR report that shows columns as represented in the table below.

The Leave Cycle Start Date and based on the Employee Start Date and Increments by 1 year for as long as an employee is with the Company. The First Cycle End Date is derived from 1 year from Employee Start Date and Iterates by 1 year until employee Leaves the Company. I have The Employee Start Date and i need to have calculated values for Cycle Start and Cycle End Dates. These 2 do not exist in the database

Kindly assist

PERSON ID	EMPLOYEE START DATE	LEAVE CYCLE START	LEAVE CYCLE END
001	01/JUL/84	01/JUL/84	01/JUL/85
001	01/JUL/84	01/JUL/85	01/JUL/86