Dates are the integral part of any data analysis. They help in analyzing the impact of something over a period of time. Thus, it becomes important to handle dates effectively to get actionable business insights. When there is only one date field in the data model, you don’t need to worry, because a single master calendar can serve the requirement, but when there are multiple dates involved in data model, depending on business requirements, you can use one of the following two options:

1. Create a separate master calendar for each date: When the business requirement doesn't want to have a common calendar for multiple dates, you should use the separate master calendar for each of the dates. Because you do not have a common master calendar, users may get confused between the master calendars of different dates. Consider the following case:

You can see in the preceding diagram that the Orders table has Order Date and Shipped Date, whereas the Invoice table has Invoice Date. Each of these date fields have their own calendars linked to them. This can help you answer such questions as, can you show me the invoice month of the orders placed in January 2006? Let's see the script in the following screenshot:

The following screenshot shows the answer of the question we asked:

2. Create a link table that can connect one date to multiple: The previous method fails when you want to see the trend of order amount versus the invoice amount in a single chart. This is because both the tables have different calendars. To solve this, you need to create a link table that links all the dates together and creates a common calendar.

You need to follow these steps to create a canonical calendar:

1. Identify the table that has a fine granularity and can easily identify all the dates. This means each record should have only one value of each date (OrderId in the following example).
2. Create a link table using the common key and date from each table (the link table in the following example).
3. Add an identifier to know which date we are referring to the (the DateSource field in the following example).

Let's see the script for the same data source we used:

The following screenshot shows the model view after reloading the preceding script:

Let's see if we can plot the trends of Order Amount and Invoice Amount in a single chart with a common time dimension. The following screenshot shows the trends: