Defining a date table

Tabular models require a table to be designated as a date table in order for DAX calculations to perform correctly. A date table can be unique for each solution and be simple or complex as your business needs require.

Getting ready

For this recipe, you will need to create a date table in your SQL Server database called MasterCalendar_T. The script that you run will create this table and populate it with data from 1/1/2006 to 12/13/2016. Once created you are ready to add the MasterCalendar_T table to your model and designate it as a date table.

First, create the table in an SQL Server database to store the calendar information:

CREATE TABLE [dbo].[MasterCalendar_T]( 
  [MasterCalendarKey] [int] NULL, 
  [Date] [date] NULL, 
  [Year] [int] NULL, 
  [Quarter_Name] [varchar](2) NULL, 
  [Quarter_Num] [int] NULL, 
  [Month_Name] [nvarchar](30) NULL, 
  [Month_Num] [int] NULL, 
  [Day_Name] [nvarchar](30) NULL, 
  [Day_Num] [int] NULL...