Scenarios for Using Calculated Tables in SSAS Tabular 2016 or Power BI Desktop – Part 2

By:   |   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:

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:

Initial date dim

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.

Mark as date table

In the pop-up you can confirm the date column as the unique identifier.

mark as data table

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:

add year column

Another option is to add new columns as regular calculated columns to the table:

add month column

The problem is those calculated columns aren’t added to the formula of the calculated table:

data 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.

final date table
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Koen Verbeeck 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



Comments For This Article

















get free sql tips
agree to terms