Power Pivot Principles: Importance of Contiguous Dates

Welcome back to our Power Pivot blog. Today, we discuss why it is important to have Contiguous Dates in a calendar table.

Before we begin, a contiguous date table is a table that stores all of the dates with no gaps (i.e. no missing dates). In addition to being contiguous the table’s dates also need to be in strict ascending order and contain no duplicates.

Example

Let’s go back to our example using the TOTALYTD function. This time, we will create a measure with the TOTALYTD function where the dates expression is not linked to a contiguous date table, instead we’ll link it to the ‘OrderDate’ column in the ‘Sales’ table (say). Therefore, that the measure is easily recognisable, we will call this measure ‘Total Sales YTD 3’:

The resulting PivotTable results will look like this:

Do you see that the ‘Total Sales YTD 3’ measure returns with the actual sales for each of the months and not the year to date sales? This is because the dates in the ‘OrderDate’ column are not contiguous (there are dates with gaps in them, for exaple), thus causing the functions in Power Pivot to break and return with unexpected results. Using a contiguous date table instead will allow Power Pivot to correctly calculate the YTD figures:

Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.