Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

Using the CALENDAR function

You can also create a date table using the CALENDAR function in DAX. This function uses a begin date and end date in the arguments to create a table for all dates between the range given. If you need a complete table with all dates represented, using this method is quick and effective.

To demonstrate how this works, you will create a date table with 10 consecutive dates.

How to do it...

1. In Visual Studio, open the Table menu and then select New Calculated Table.
2. This creates a new table in the project that requires a DAX expression to populate the table.
3. To create a row for each row between 1/1/2006 and 1/10/2006, use the CALENDAR function:

           =CALENDAR("1/1/2006","1/10/2006")

4. Now mark this table as the date table in the model. ...