Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

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] ...