Time Intelligence is a set of techniques for performing time and date-based calculations. It covers a broad set of calculations, most of which are needed for business reporting. You can perform all of these calculations using the DAX language and Power Pivot in Excel, or in a Tabular Model in SQL Server Analysis Services.

As an example, you might want to compute a simple formula, like Year-To-Date, to aggregate sales from the beginning of a year. Alternatively, you may need to compare like-for-like periods, such sales in the current month with the same month in the previous year. Perhaps you have to perform calculations over ISO weeks (including 445, 454 or 544 formats), or over another, more complex calendar. Any of these computations are typical examples of time intelligence.

Let Alberto Ferrari, book author and a renowned Data Analysis Expressions expert, show you an in-depth tour of these functions, in this 1-hour video that contains 6 detailed, hi-def demos. First, you will learn how to correctly define a calendar table, and any needed additional role dimensions, and how to leverage the simpler, built-in, time intelligence DAX functionality, including TOTALYTD, TOTALQTD, and TOTALMTD. Subsequently, you will see how to deal with more complex scenarios, necessitating the use of the CALCULATE function, by writing your own time intelligence calculations, using both predefined and custom filters. Finally, you will learn how to correctly handle semi-additive measures, such as last-day, or last-non-empty aggregations.

We suggest that you view the complete video throughout, to learn about the different techniques, and then come back later, when you need to implement a specific calculation. Time intelligence is a set of patterns you will find incredibly useful, and by adjusting the code provided in this video you will be able to handle your unique scenarios all by yourself.

Log in or purchase access below to the premium version of this content.