Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments   |   Related Tips: 1 | 2 | 3 | 4 | > Analysis Services Development

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools