Power and Simplicity of Time Intelligence in DAX

  • Article

Almost all data analysis have to deal with dates/time. DAX offers a number of functions that simulate many calculations on dates, but using right function in the right way requires some explanation.

Defining a separate date table is a common practice in any star schema and this is a suggested technique for any tabular model. Thus, whenever you have a date column that you want to analyse, you should create a relationship with a date table. If you have multiple date columns in a table, you can create multiple inactive relationships to the Date table plus a single active one.

Creating a Date table

An obvious question will be that what is the best way to create a Date table? Well, you have atleast three options:

  • ·        Generate Dates in a SQL Query or View
  • ·        Generate Date table in MS Excel and import it in your tabular model
  • ·        Import a Date table from DateStream on Windows Azure Marketplace

Generate Dates in a SQL Query or View

While there are many ways to create this, I normally use this script

 

 

Good thing about this script is that, it has Date_ID column which is int, making it possible to relate it to date columns in any format.

Generate Date table in MS Excel and import it in your tabular model

You can create a column containing dates and then add other columns like day, month, year, etc using Excel formulas that produce these values.

Import a Date table from DateStream on Windows Azure Marketplace

Datastream is a free data source available on Windows Azure marketplace whose source code and documentation is available on https://datastream.codeplex.com

 

Setting Metadata for Date table

Many DAX functions that operate over dates requires a Date table having a Date Type column. In order to mark a table as Date table,

(1)    Open your tabular model in SQL Server Data Tools

(2)    Select your Date table

(3)    Right-Click and select “Mark as date Table”

 

  

 

(4)    Select the Date Type column and click “Ok”

 

Year-to-Date, Quarter-to-Date and Month-to-Date functions

These calculations are pretty common during analysis and are very similar in nature. While Month-to-Date function is used for analysing data at the day level, quarter-to-date and year-to-date functions are used for analysing data at the month level.

One way to achieve this, is to use CALCULATE and DATESYTD functions.

[YTD Sales] := CALCULATE([Internet Total Sales], DATESYTD(‘Date’[Date])

DATESYTD is a built-in time intelligence function that returns a list of all dates from the beginning of the year until the last date included in the current filter context. This list is passed as an argument to the CALCULATE function to set the filter for the Internet Total Sales calculation.

 

 

Since this patter is very common, DAX offers a dedicated function that simplifies this syntax!!

[YTD Sales] := TOTALYTD ([Internet Total Sales], ‘Date’[Date])

[QTD Sales] := TOTALQTD ([Internet Total Sales], ‘Date’[Date])

[MTD Sales] := TOTALMTD ([Internet Total Sales], ‘Date’[Date])

 

Period from Prior Year

Users and analysts usually need to get values from a period of the prior year. This is useful for making comparisons of trends during a period last year to the same period this year. Thus is the DAX function SAMEPERIODLASTYEAR that returns a set of dates shifted one year back in time. This is a specialized version of the more generic DATEADD function.

[Last Year Sales] := CALCULATE ([Internet Total Sales], SAMEPERIODLASTYEAR (‘Date’[date]))

Sometime you may want to look at a measure for the previous year, usually to compare it with the year-to-date total. To do that, you can use another specialized function:

[Last Year Sales] := CALCULATE ([Internet Total Sales], PARALLELPERIOD (‘Date’[date], -1, Year))