SSAS Tabular Time Intelligence

02-09-2019  0 Comment(s)

Time Intelligence is a common methodology in Dimensional Modeling that allows for calculations of measures based on relative dates. These include “To-Date” measures like Year to Date or Quarter to Date as well as Previous Period comparisons. Once these are created within a model, other DAX functions can be used for metrics like Year over Year (YoY) growth and YoY change including percentages. A Key Performance Indicator (KPI) can be used for percentage gain or loss on YoY ratios. These calculated measures help businesspeople see metrics as a summarization rather than row by row report type results. Using a report tool like Excel or PowerBI can enhance the results making them easier on the eyes.

Date Table

Before starting with Time Intelligence, this is a reminder that there must be a table designated as the Date table in order to use Time Intelligence in Analysis Services. The previous article on the date dimension can get a project up to speed for the configuration of the date table. Figure 1 shows this project’s Data table. If you have followed the series to this point, the project will be the same.

Year (YTD) and Quarter to Date (QTD)

The first Time Intelligence functions demonstrated will be Year to Date and Quarter to Date. Figure 2 shows an example of using these DAX functions on the Total Sales created. The Date Dimension is already configured for Time Intelligence and a hierarchy with Year-Quarter-Month-Day (Y-Q-M-D) has been created. This example (Figure 2) is an Excel pivot table is connected to the Analysis Services analytical database.

The rows in Figure 2 are using the Y-Q-M-D hierarchy with the measures as columns, created in the previous article. There is a separate measure for Total Sales versus Sales YTD versus Sales QTD. In this example, the fact for Sales has an Order Date related to the Date Dimension table by a surrogate key. This relationship enables the DAX function to use the Time Intelligence configuration from the Data dimension.

You can see in Figure 2 the Sales YTD value continues to accumulate with the row at Q2 in 2015. But, the Sales QTD measure resets once the new Quarter, Q2, starts in a preceding row. The same can be done for the month within each quarter with the DATESMTD DAX function.

Figure 3 shows the measures created in a Tabular Model project. This is the first use of the CACLULATE DAX function. CALCULATE is necessary for using measures with a previous calculation (SUM, COUNT, etc. or computations like ListPrice * Quantity) to be used in the Time Intelligence functions. Be sure to format each as Currency.

Sam Analytiks motivates, educates and proliferates data for any organisation as a non-profit partner. If you or any of your team member needs a help, surely our consultants will be glad to help you in any case.

Call us at +48-729473572 or email us at
#data #datavisualisation #datamodelling #datamart #powerbi #sql #excel #powerquery #azure #azureanalysis 

Comment Here


No Comments to Show