Ways to model different DAX calendar date situations
Part two of a six-part series of blogs

In the real world (or at least, the DAX version of it), calendars and dates aren't quite as simple as one would like. This blog shows how to solve a number of common scenarios, including where different tables have different levels of "granularity", coping with special days like bank holidays, handling non-standard finanical year-end dates and dealing with a table which contains more than one date column.

  1. Modelling techniques when working with calendars/dates
  2. Dealing with varying date granularity using calendars (this blog)
  3. Modelling bank holidays and other special days
  4. Modelling different financial year-ends
  5. Handling multiple date columns using multiple tables
  6. Handling multiple date fields using multiple relationships

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 22 January 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Dealing with varying date granularity using calendars

In the real world, not all data is tidily organised.  This page shows how to cope with this!

Creating the worked example table of forecast data

You can try this out yourself by running this SQL Server script to generate this table of forecasts:

Table of forecasts

The table of forecasts generated by this script.


You can then import this into SSAS Tabular to get this table:

Forecasts in SSAS

The same table imported into your tabular model.


The aim: compare quarterly forecast data with total sales by quarter

This blog shows how to generate a pivot table comparing actual and forecast sales by species and quarter:

Actual vs forecast

This page will show how to compare actual and forecast data (here we're just showing the Amphibian and Bird species).

Generating a date for each forecast row

The simplest way to link the imported forecast table to your calendar table is to create a date column for each quarterly sales forecast row:

Formula giving date

This calculated column returns the first date for each quarter.

This formula takes each quarter, extracts the right-most character (the quarter number) and generates a month number from this which feeds into a date formula:



right( [Quarter], 1) * 3 - 2,



The formula calculates the first date for each quarter.  It doesn't matter which date you construct, as long as the quarter it belongs to is correct.

Generating the measures needed

To compare total forecast data for a quarter with total actual sales for the quarter, we'll need two measures.  Here's the measure for the forecast table:

Summing forecasts

The measure sums forecasts, but if we display data by quarter and species each cell will actually only "sum" a single value.

The measure for the transactions table to show total sales could look something like this:

Sum of amount

This measure shows total sales (price multiplied by quantity).

Creating the relationships

In addition to the existing relationships in your model, you now need to create some additional ones:

Forecast relationships

For each forecast we can pick up on the species it applies to and for which calendar date it was made.


Generating the pivot table

You can now generate the pivot table comparing actual and forecast sales:

Pivot table field list

You can summarise the forecast and actual sales by year and quarter.


Note that if you delved any further into the calendar (for example if you tried to summarise by month) you'd get strange results, because the forecast data was only available at quarter level.  It's up to you not to ask for silly pivot tables!

Here's the pivot table (having filtered it to show only amphibians and birds):

Pivot table

The pivot table, comparing the quarterly forecast with the sum of actual sales for each quarter.

You may be worrying that attempting to show forecast data at monthly or daily level will give meaningless or even misleading data, but there's an easy way to avoid this happening; just take care not to expose the forecast figures and months/dates in the same model perspective.

This blog has 0 threads Add post