What is Time Intelligence?

28 August 2013 · 2234 views

Loading the player...

If this message stays on, check that you have the latest Flash Player installed (http://get.adobe.com/flashplayer) and that your browser supports JavaScript.

Using DAX Language for Time-based Calculations

Time Intelligence simply means doing BI calculations over periods of time, or over dates. For example, a common request in many reports is to show a value, such as Sales, aggregated month-by-month, from the beginning of each year—so that you know, in this case, what were the total sales, in that year, up to the given month. DAX, Data Analysis Expressions, the language of Excel 2013, Power Pivot in Excel 2010, and in SQL Server Analysis Services (2012 or newer), includes many predefined time intelligence functions, including TOTALYTD—which is exactly the function you need to use in this particular scenario.

Let Alberto Ferrari, best-selling author of books on DAX, and a renowned SSAS expert, show you (0:21) how to use TOTALYTD in this short video, in which he uses it to compute Total Sales on a monthly basis, resetting automatically at the beginning of each year.

If you find this useful, however, make sure to learn about the remaining Time Intelligence functions, including the use of CALCULATE with predefined and custom filters, dealing with week numbers (including ISO weeks), or computing semi-additive measures, such as balances, including last-day, or last-non-empty aggregations—all of which Alberto explains in great detail in his 1-hour DAX: Time Intelligence video training module, part of our online Data Analysis Expressions course.