O'Reilly logo

Tabular Modeling with SQL Server 2016 Analysis Services Cookbook by Derek Wilson

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required