BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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.
- Modelling techniques when working with calendars/dates (this blog)
- Dealing with varying date granularity using calendars
- Modelling bank holidays and other special days
- Modelling different financial year-ends
- Handling multiple date columns using multiple tables
- 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.
Modelling techniques when working with calendars/dates
The previous blog in this series showed how to create and use a basic calendar. This blog shows how to cope with the following scenarios which can arise in "real life":
| Scenario | Notes |
|---|---|
| Varying date granularity | How to cope when some data is at date level, some by month and some by quarter. |
| Bank holidays | The best way to integrate special days like bank holidays into your calendar. |
| Financial years | Coping with the common scenario that your financial year doesn't end at the same time as your calendar year. |
| Multiple date tables (1) | Using multiple tables to cope with the situation where a single table contains two or more dates (for example, an order table might have an order, shipping and invoice date). |
| Multiple date tables (2) | Using multiple relationships as a way to model the same thing, but this time including your calendar table only once. |
Even if your interest in calendars is waning it's still worth reading this blog, as many of the techniques introduced have much wider application within SSAS Tabular.
- Modelling techniques when working with calendars/dates (this blog)
- Dealing with varying date granularity using calendars
- Modelling bank holidays and other special days
- Modelling different financial year-ends
- Handling multiple date columns using multiple tables
- Handling multiple date fields using multiple relationships