In Power BI, time intelligence refers to a set of built-in functions to assist with common date based aggregation and slicing scenarios. As you can see, there are quite a few of these functions with names that give a good indication of their purpose, like opening and closing balances for months, quarters, and years. There are functions for totaling those same periods, as well as next and previous period functions. In this demonstration, I want to look at a common scenario where we can utilize some of these functions to produce a typical sales report over time and compare year-on-year and month-on-month growth.

I'll start with a report table, drop my date hierarchy onto it, and remove all of the fields except for year month, which is the date year multiplied by 100 with the month number added to it. Next, I'll add my sales measure, a simple sum of the LineTotal field, so we're starting with sales summarised by month. Next, I'll add a year-to-date sales measure using the TotalYTD function that takes an expression as its first parameter, which will be the sales measure, followed by a date series. When you see the argument dates, as opposed to date the singular, you need to supply a series of dates, as in dates from a date field. We can see that the TotalTYD function cumulatively sums the current period up until the end of the calendar year. Where the dataset starts and the month equals one or January, the sales and sales year-to-date figures are the same. Otherwise, each year-to-date row is the Sum of previous months' sales up until December or month 12. I can do the same with the TotalQTD function even though quarters aren't defined in my FiscalYear dates table as I'm using date, the most granular field, and Power BI takes care of the rest. This organization also categorizes sales by weeks, where week one is the beginning of the financial year that starts on the 1st of July. Looking at week 53 in June 2009, we can see the sales year-to-date is still accumulating, which is not what we want if our fiscal year ends in June. I'll create another column, sales fiscal year-to-date, which will use the same TotalYTD function, but this time I'll add a year end date using double quotes with the date specified as month-year. In the TotalYTD function definition, you can see that there is another optional parameter, filter appearing before year end date. You don't need to add an extra comma or a placeholder parameter as Power BI can tell what you mean from the parameter data type. Now we can see sales fiscal year-to-date resetting to the 1st of July value.

Another common report requirement is comparing values from one period to the corresponding previous period. I'll create a year-on-year measure using the calculate function, specifying the sales measure as my expression and the SamePeriodLastYear function, passing in the date field from FiscalYear. I'll get rid of the week number column to see better what's going on and make the columns the same precision for easy comparison. To see the year-on-year growth is a simple case of subtracting the year-on-year measure from the original sales measure. While the year-on-year growth figures in the first financial year may be technically correct, you probably don't want to see them in most scenarios. This is an easy enough to fix. I'll wrap the expression in an if statement, testing for a blank value. We've got our year-on-year column, but the rows are by months, so ideally, you would also like to see month-on-month data. Using the calculate function specifying sales as our expression, this is easy to do using the generic function ParallelPeriod. ParallelPeriod takes dates as the first parameter, then the interval offset, followed by the type of interval. So with months as our interval, we can use -1 as an offset to look back one month. The month-on-month column now displays the same value as the sales column but from the previous month.