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. Select the Table menu and then Date and Mark As Date Table:

   

5. Create the relationship from the Calc_Date_T table to the CRASH_DATA_T on the CRASH_DATE table:

   

How it works...

The CALENDAR function is the best method to create a new calendar...