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

Get Tabular Modeling with SQL Server 2016 Analysis Services Cookbook now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.