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

 

SQL Server Analysis Service DAX Time Intelligence


By:   |   Read Comments (4)   |   Related Tips: > Analysis Services Development

Attend these FREE MSSQLTips webcasts >> click to register


Problem

What date related functions are available in DAX for the Tabular Model version of SQL Server Analysis Service (SSAS)?

Solution

On MSSQLTips, there are many great articles which provide information about the SSAS Tabular Model. I would start with:

These articles give you a good basis for understanding how the Tabular Model differs from the more traditional MultiDimensional Model. Using the tabular model we are able to utilize in-memory performance and query advantages to provide inputs for Excel Add-in tools such as PowerPivot or PowerView. In either case we can utilize Excel 2010 (OR Excel 2013 if you have a specific volume license) to show you how to utilize some of the common Date and Time Intelligence features of the Tabular Model.

Time Intelligence tools allow for easy comparison of figures such as year over year growth, month over month growth, month-to-date, year-to_date, and parallel period comparisons. You can follow along the examples include in this tip by downloading and installing the ContosoV2SQL SQL 2012 database from http://excelservicesppdata.codeplex.com/releases/view/100981. Alternately, if you do not have a SQL 2012 instance readily available, you can also download the ContosoV2 zip from http://powerpivotsdr.codeplex.com/releases/view/35434; this download contains a Contoso Access database and also two Excel starter files which can be used as the source for PowerPivot. We can import the data tables from either the Access DB or the SQL2012 DB directly into PowerPivot within our Excel file. Last, you can start from scratch, and use Tabular Model example, also found on CodePlex at: http://msftdbprodsamples.codeplex.com/releases/view/55330. From that point, we will expand our knowledge base by exploring the various ways to use DAX Time Intelligence functions to explore our data.

DAX Time Intelligence

DAX functions are a formula based set of functions very similar to the functions used in Excel; DAX is designed to be used in conjunction with PowerPivot, PowerView or PivotTables. The 30 plus DAX Time Intelligence functions actually can be grouped into a few majors areas:

  • Some of the functions return a single date such as the same date one year prior
  • Some of the functions return a group of contiguous or non-contiguous dates
  • Some of the functions return a period of dates

In many if not all cases, these functions are used in conjunction with or as an input for other DAX Functions, such as a calculation. Additionally as we think about the Time Intelligence functions, their context is tied to the current row unless we specify otherwise in the formula. However, the results can be a single value, a single row in a table, or even a table, set, or range of values. In the below examples we will not cover all the functions, but we attempt to cover examples from each of the various function groups. Furthermore, we will use Excel 2010 and PowerPivot for our examples as most folks are likely still using this version and have not moved to 2013 quite yet. If you do move to Excel 2013, please be sure to review this note about what versions of Excel include PowerPivot.

The first set of functions we will review are those that return generally a single value or a single row if a table is returned. Some examples of these functions include ENDOFMONTH, which returns the last date in the month for the current context of the date, and PREVIOUSDAY which returns the day just prior to the input date. The syntax for ENDOFMONTH is: ENDOFMONTH(<dates>) while the syntax for PREVIOUSDAY is: PREVIOUSDAY(<dates>). Using these two functions, the formula and results are fairly simple and the results expected; based on the DateKey column, the PREVIOUSDAY function returns on day prior to the DateKey. The ENDOFMONTH function returns the last day of the month for the related DateKey on the same row. Notice how in the formula we are actually using the DimDate table as the basis; we are using this field as the DimDate table has all sorts of variations of date values such as Month, Quarter, Year, etc which are helpful in PivotTable slices. However, by using the DimDate table, the context of the data we include on our pivot tables needs to also be within the DimDate table context. Following the next example, we will show where the formula context produces incorrect results.


Previous Day

End of Month

Certainly, theses values are informative and could be used in your pivot. However let us put them to even better use, by creating a new "calculated measure" that provides us with the prior days Sales Amount. In order to create this measure we have two methods we can follow to achieve the same results. We can either create the measure on the calculation area of the PowerPivot Window or we can do it within the pivot table itself. In order to create this measure, we have to employ two additional DAX functions, CALCULATE AND SUM. The SUM function does exactly that, it sums the values in the specified measure while the CALCULATE functions filters the values used in the SUM function.

The actual formula with all three functions is:

=CALCULATE(SUM(FactSales_Limited[SalesAmount]),PREVIOUSDAY(DimDate[Datekey]))

The first method for adding a measure is using the Calculation area of the PowerPivot window. To open up the PowerPivot window, select PowerPivot from the ribbon tabs, and then select Power Pivot Window.

Power Pivot Button

Once you click PowerPivot Window, a new screen will open with your imported data sets (the data from the Contoso database if used). In order to create calculations, you actually need to open up the calculation area which appears below the data sets. If the calculation area is not visible, then it can be toggled on using the Calculation Area button as noted in the below illustration.

Power Pivot Calculation\

The Calculation area appears below the gray line in the above illustration. Each cell can contain a separate calculation; the calculations do NOT need to relate to the data in the same relative columns above the gray line. The actual formula can be entered in a cell itself or in the function box (f of X box) once the cell is selected.

The second method to create a new measure uses the New Measure button on the PowerPivot ribbon within the main Excel window. In order to use this method, again select PowerPivot from the ribbon tab menu, and then click on the New Measure button, as noted below.

New Measure Button

On the Measure Settings screen, select the table where the measure will reside. Next fill in the Measure Name and Custom Name (if desired); next add the formula noted above, and finally in the category box, select the category type. For this example, the measure category is number which allows us to define the format for the measure.

Pivot Table Measure Add

Finally we can create and populate our pivot table to show the fruits of our work in creating this new measure. For this example add the Datekey field from the DimDate table to the Row Labels area of the Pivot table. Next add the Sales Amount and PreviousDaySales field from the FactSales table to the values area.

Add Date KeyAdd Sales and Previous Day Sales

Below, the resulting pivot table shows the current day sales amount and then the previous day sales amount as illustrated below.

Previous Day Pivot Result

A few items need to be mentioned about our new measure specifically related to the context or scope level used in the pivot. First take a look at what happens in the below example when we use the DateKey field from the Fact Table instead of the DimDate table which was specified in the measure formula. When we switched to the FactSales DateKey, our Previous Day Sales figure disappears, because the measure is evaluated at a different context or scope than the DateKey field used.

Incorrect Date Syntax

In a similar fashion, even if we use the DimDate table, but switch our row label to Calendar Year, the Previous Day Sales value do not bear out. Again the context of the Previous Day Sales is at the DateKey Level, but in the below example we are attempting to view the date in context of the calendar year. You can explore more details and hints about DAX context at: http://technet.microsoft.com/en-us/library/gg413423.aspx.

Calendar Year context

Time Intelligence Functions that Return a set of Values

Several of the functions in the Time Intelligence Category actually return a table or array of values. When these functions do return a set of values, they cannot be added as a column on the PowerPivot table as an error will result. Instead, we can again use these functions as inputs into another DAX function. Using the NEXTMONTH function, the below formulas, when added as new measures, will return the Total Sales and Average Sales for the "next" month. Using this logic the value will be the same across all dates for the month.

=CALCULATE(SUM(FactSales_Limited[SalesAmount]),NEXTMONTH(DimDate[Datekey]))

or

=CALCULATE(AVERAGE(FactSales_Limited[SalesAmount]),NEXTMONTH(DimDate[Datekey]))

These formulas result in the following pivot table; notice how the roll up of the sales values by month matches the value returned for the Totals Sales Next Month measure. For the average calculation, note that this value is the average of the individual Sales Amount lines in the table and not the total sales for the day.

Next Month

Similarly, instead of moving one day or one month, we could use the DATEADD function to move x number of days forward or backward, and get the daily sales value for that day. Our next formula finds the sales amount 10 days into the future.

=CALCULATE(SUM(FactSales_Limited[SalesAmount]),DATEADD(DimDate[Datekey],10,DAY))

Using the DATEADD function in a measure returns the following pivot table.

Date Add Example

This type of formula generally works well in period over period type of calculations, such as day over day growth or year over year variance analysis. Some of the other functions that also perform well in these situations include: PARALLELPERIOD, NEXTYEAR, NEXTQUARTER, PREVIOUSYEAR, PREVIOUS QUARTER, PREVIOUSDAY, DATESMTD, DATESYTD, DATESBETWEEN, and DATESINPERIOD.

"No Calculate Needed"

Some of the Time Intelligence functions actually return a measure value and are not required to act as an input for other functions such as CALCULATE. These functions are actually what I like to call short cut functions as they make the formulas much simpler than the longer CALCULATE method. These functions include: TOTALMTD, TOTALQTD, and TOTALYTD.

The below example uses the TOTALMTD to source a running total of SalesAmounts.

=TOTALMTD(SUM(FactSales_Limited[SalesAmount]),DimDate[Datekey])

The TOTALMTD function allows for the quick review of the total month to date sale amounts.

TOTALMTD

Some additional functions that fall under the "short cut" category include: OPENINGBALANCEMONTH, OPENINGBALANCEYEAR, CLOSINGBALANCEMONT, and CLOSINGBALANCEQUARTER. These functions serve very specific purposes related most often to financial statements and inventory type calculations

Conclusion

DAX includes a large range of Time Intelligence related functions; these functions allow PowerPivot designers to quickly navigate a range of dates. Some of the functions work well as arguments for other DAX functions while another set allows for direct calculations against a measure value. Furthermore, some of the functions return a single value and can be added as a column within the PowerPivot table. To the contrary, another group of the functions return a set or table of values which can generally only be used as inputs into other DAX functions. In either case, special care must be taken to note the context of the functions and formulas as not adhering to the a specific scope or context could produce irregular results or no results at all.

Next Step


Last Update:


signup button

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





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     



Friday, August 01, 2014 - 8:29:42 AM - Scott Back To Top

Ashwini.. might want to look at: http://blogs.msdn.com/b/analysisservices/archive/2010/04/12/time-intelligence-functions-in-dax.aspx


Friday, August 01, 2014 - 6:10:34 AM - Ashwini Back To Top

Hi,

 

 

I want the  Year over year formula i.e DAX Function.

 

 

 

 

 

 


Friday, October 18, 2013 - 10:45:07 AM - Scott Murray Back To Top

ndy raises a good point even if you are using multi dimensional or tabular..  445 calendara create interesting problems.


Friday, October 18, 2013 - 7:33:37 AM - Andy Wilbourn Back To Top

Just to note to others, these Time calcs are only for Calendar year. If you have some other accounting periods, it will not work. We have a 4-4-5 accounting period and had to follow some instructions posted by Scott Russo to make things work. It would be helpful if the tabular would let you define which columns are the Year, Qtr, month, etc and then tell it what type of accounting period you follow (like what Cognos cubes offer, but not going to go there right now). If that did this, then it would make things a bit easier to use. In our solution we had to expose the day of the fiscal year from our date dim and then to do a year of year comparison it takes that into consideration. You have to defined all your own custom calcs, which is what makes it a pain.


Learn more about SQL Server tools