By: Koen Verbeeck | Updated: 2017-06-21 | Comments | Related: 1 | 2 | 3 | 4 | > Analysis Services Development
Problem
SQL Server Analysis Services 2016 comes with a lot of DAX improvements. One of these improvements is the ability to create calculated tables. In this tip, we’ll explore the use case where we generate a date table in the model.
Solution
With the release of SQL Server Analysis Services 2016 (SSAS), you can create custom calculated tables using the DAX query language. The tip How to create calculated tables in a tabular SQL Server Analysis Services SSAS model explains how you can create a calculated table in SQL Server Data Tools (Visual Studio) for a SSAS Tabular project. The same is possible in a Power BI Desktop model.
In the series of tips, we’ll cover the following topics:
- Creating small lookup tables
- Generating a Date table, which is the subject of this tip
- Calculating an Aggregate table
Generating a Date Table
A date table is the corner stone of every star schema model. Almost all analysis tends to be created with time as one of the dimensions. In DAX, certain time intelligence functions count on the presence of a date table with the following properties:
- A continuous range of dates, meaning there are no gaps in the date sequence
- There is one row for every date
- One column which is of the date data type
Let’s start with creating the range of dates:
= CALENDAR (DATE(2000;1;1); DATE(2025;12;31))
The result is as follows:
Keep in mind that in DAX the delimiter in functions can change between the comma and the semicolon depending on your locale. In Power BI Desktop, you can change the data type to date, which differs from date/time. In SSAS Tabular you only have the date data type at your disposal. You can mark the table as a Date Table, which allows time intelligence functions to work properly. In Power BI Desktop this option is not present and you might need to resort to a work around.
In the pop-up you can confirm the date column as the unique identifier.
As a next step, we can add more descriptive columns to our date table.
= ADDCOLUMNS(CALENDAR (DATE(2000,1,1), DATE(2030,12,31))
,"Year",YEAR([Date]) -- Year column
)
You can do this directly in the definition of the calculated table:
Another option is to add new columns as regular calculated columns to the table:
The problem is those calculated columns aren’t added to the formula of the calculated table:
This makes it harder to copy-paste your formula from one model to another. If you want portable code, you might want to keep all columns in the original DAX statement of the calculated table.
The final code might look like this:
= ADDCOLUMNS (
CALENDAR (DATE(2000,1,1),DATE(2030,12,31)) -- date column for time intelligence e.g. 2017-06-04 12:00:00
,"Year",YEAR([Date]) -- e.g. 2017
,"MonthOfYear",MONTH([Date]) -- e.g. 1,2,... ,12
,"Month",FORMAT ([Date],"YYYY/MM") -- e.g. 2017/06
,"YearMonthShort",FORMAT([Date],"YYYY/mmm") -- e.g. 2017/Aug
,"MonthDescShort",FORMAT([Date],"mmm") -- e.g. Aug (combine with MonthOfYear column for proper sorting)
,"MonthDesc",FORMAT([Date],"mmmm") -- e.g. August
,"DayOfWeekNumber",WEEKDAY([Date]) -- e.g. 1,2,3 ... 7 (Sunday = 1)
,"DayOfWeek",FORMAT([Date],"dddd") -- e.g. Monday
,"DayOfWeekShort",FORMAT([Date],"ddd") -- e.g. Mon
,"Quarter","Q" & FORMAT([Date],"Q") -- Q1
,"YearQuarter",FORMAT([Date],"YYYY") & "/Q" & FORMAT([Date],"Q") -- e.g. 2017/Q2
,"IsCurrentMonth",IF(YEAR([Date]) = YEAR(NOW()) && MONTH([Date]) = MONTH(NOW()),1,0) -- relative filter, is recalculated when model is processed)
In the end, a relative filter is added, which can change each time the model is processed. There are lots of similar filters possible, such as IsCurrentYear, IsPreviousYear, IsCurrentWeek etc. These are left as an exercise for the reader.
Next Steps
- Try to create a couple of DAX calculated columns to enrich the date table.
- More information on generating date dimensions:
- You can find more Analysis Services tips in this overview.
- For more SQL Server 2016 tips, you can use this overview.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-06-21
