One of the most common calculations performed in data analysis is to compare some number to a comparable number for a different time period. Calculations that make comparisons to last month or to the same period from a year ago are very important for any business intelligence tool. Toward that end, DAX introduces 35 new functions expressly for the purpose of working with time based data.
Whenever you are defining measures and working with time periods, there are many opportunities to make bad assumptions about what the PivotTable in which the measure will be used might look like. To avoid a lot of those problems, DAX makes certain assumptions and we recommend certain best practices for anyone planning to use the time intelligence functions described here.
We recommend that you create a table in your PowerPivot data that contains one row for every date that might exist in your data. Many people think of this as a date table or a time table or a time dimension. The notion is that this table has one row for each date, and there should be a many to one relationship from any date columns in the database to this date table. Building such a table in Excel is fairly trivial to do, and it might look like the table below, or like the DimDate table in Contoso.
DAX uses this table to construct a set of dates for each calculation. The only column that matters is the date column itself. There are no requirements whatsoever about any of the other columns, and they aren’t needed by DAX, although you may find them useful when you build PivotTables. You need to provide a reference to the Date column as an argument to every one of the 35 time intelligence functions in DAX.
In DAX, we always calculate a set of dates as a table and then use that as if it were a SetFilter argument to the CALCULATE function. Consider that a user might have multi-selected some dates within a PivotTable, so that the filter context for a calculation might be any of the following:
- A single date
- A set of contiguous dates
- A set of non-contiguous dates
- Dates that happen to correspond to a calendar month or quarter or year (very common)
Then consider that we might need to shift those dates to find the following:
- The dates that make up the previous day, month, quarter, or year
- The same dates shifted to a previous month, quarter, or year
- The same dates shifted some interval of time (14 days, 30 days, etc.)
- Dates calculated by shifting an arbitrary interval forward or backward in time
In DAX, we accomplish all of these by working with sets of dates for everything. We don’t try to know anything about month or quarter or year columns, but we do know the dates for any given month.
In the initial release of PowerPivot we do not try to handle the many custom calendars that we know exist for financial analysis, but we do allow folks to build those custom calendars by authoring custom formulas to handle such things as 13 four week months in a year, or 4-4-5 quarters, etc.
The built-in functions handle calendar or fiscal years where fiscal year is defined as having a yearend date other than Dec 31. They also know about months, so that when we shift the period April 1-30 back one month, we know that we want March 1-31, and not merely March 1-30. This requires snapping to the end of a month in a variety of situations. In general DAX handles calendar quarters as 3 months, and years as 12 months, so that all of the internal calculations are really based on days or months.
Let’s look at the specific time intelligence functions in DAX and how they work.
The functions in this category return a single date. The result of these functions might then be used as arguments to other functions.
The first two functions simply return the first (or last) date in the Date_Column in the current context. This can be useful when you want to find the first (or last) date on which you sold each product, or the first (or last) date on which you had a transaction of a particular type. These functions take only one argument, the name of the date column in your date (or time) table.
- FIRSTDATE (Date_Column)
- LASTDATE (Date_Column)
The next two functions aren’t strictly time intelligence functions, because they can be used for other purposes, but they will most often be used for time calculations. They are used to find the first (or last) date (or any other column value as well) where an expression has a non-blank value. This is most often used in situations like inventory, where you want to get the last inventory amount, and you don’t happen to know when the last inventory was taken.
- FIRSTNONBLANK (Date_Column, Expression)
- LASTNONBLANK (Date_Column, Expression)
Six more functions that return a single date are the functions that return the first or last date of a month, quarter, or year within the current context of the calculation.
- STARTOFMONTH (Date_Column)
- STARTOFQUARTER (Date_Column)
- STARTOFYEAR (Date_Column [,YE_Date])
- ENDOFMONTH (Date_Column)
- ENDOFQUARTER (Date_Column)
- ENDOFYEAR (Date_Column [,YE_Date])
There are sixteen time intelligence functions that return a table of dates. Most often, these functions will be used as a SetFilter argument to CALCULATE. Just like all Time Intelligence functions in DAX, each function takes a column of dates as one of its arguments.
The first eight functions in this category are reasonably straightforward. Each of these functions starts with a date column in a current context. For example, if we are calculating a measure in a PivotTable, there might be a month or year on either the column labels or row labels. The net effect is that the date column is filtered to include only the dates for the current context. Starting from that current context, these eight functions then calculate the previous (or next) day, month, quarter, or year and return those dates in the form of a single column table. Note that the “previous” functions work backward from the first date in the current context, and the “next” functions move forward from the last date in the current context.
- PREVIOUSDAY (Date_Column)
- PREVIOUSMONTH (Date_Column)
- PREVIOUSQUARTER (Date_Column)
- PREVIOUSYEAR (Date_Column [,YE_Date])
- NEXTDAY (Date_Column)
- NEXTMONTH (Date_Column)
- NEXTQUARTER (Date_Column)
- NEXTYEAR (Date_Column [,YE_Date])
The next four functions in this category are similar, but instead of calculating a previous (or next) period, they calculate the set of dates in the period that is “month-to-date” (or quarter-to-date, or year-to-date, or in the same period of the previous year). These functions all perform their calculations using the last date in the current context. Note that SAMEPERIODLASTYEAR requires that the current context contain a contiguous set of dates. If the current context is not a contiguous set of dates, then SAMEPERIODLASTYEAR will return an error.
- DATESMTD (Date_Column)
- DATESQTD (Date_Column)
- DATESYTD (Date_Column [,YE_Date])
- SAMEPERIODLASTYEAR (Date_Column)
The last four functions in this category are a bit more complex, and also a bit more powerful. These functions are used to shift from the set of dates that are in the current context to a new set of dates.
- DATEADD (Date_Column, Number_of_Intervals, Interval)
- DATESBETWEEN (Date_Column, Start_Date, End_Date)
- DATESINPERIOD (Date_Column, Start_Date, Number_of_Intervals, Interval)
- PARALLELPERIOD (Date_Column, Number_of_Intervals, Interval)
DATESBETWEEN calculates the set of dates between the specified start date and end date. The remaining three functions shift some number of time intervals from the current context. The interval can be day, month, quarter or year. These functions make it very easy to shift the time interval for a calculation by any of the following:
- Go back two years
- Go back one month
- Go forward three quarters
- Go back 14 days
- Go forward 28 days
In each case, we only need to specify which interval, and how many of those intervals we want to shift. A positive interval will move forward in time, while a negative interval will move back in time. The interval itself is specified by a keyword of DAY, MONTH, QUARTER, or YEAR. Note that these keywords are not strings, so they should not be in quotation marks. It’s also useful to note that auto-complete doesn’t yet help to fill in these keywords, so you simply have to type them in.
Let’s look at some examples of how these functions might be used with the Contoso data set:
Year over Year Growth
The problem I want to solve is to calculate year-over-year growth for Store Sales. Store Sales is defined as follows:
[StoreSales] = CALCULATE (SUM (FactSales[SalesAmount]), DimChannel[ChannelName]="Store")
In other words, Store Sales is defined as the sum of the SalesAmount column in the FactSales table, with the context modified to include only sales where the Channel Name in the DimChannel table is “Store”.
Now we want to calculate StoreSales for the previous year, using this formula:
[StoreSalesPrevYr] = CALCULATE( [StoreSales], DATEADD(DimDate[DateKey], -1, YEAR))
Since we’re calculating a single measure in a modified context, this can be written using a syntax shortcut.
[StoreSalesPrevYr] = [StoreSales]( DATEADD(DimDate[DateKey], -1, YEAR))
Now we can calculate Year over Year Growth by simply subtracting last year’s sales from this year’s sales and showing that difference as a percentage of last year’s sales.
[YOYGrowth] = ([StoreSales] – [StoreSalesPrevYr])/[StoreSalesPrevYr]
Finally we need to wrap this in an IF statement, so we don’t get division by zero in the first year.
[YOYGrowth] = IF ([StoreSalesPrevYr], ([StoreSales] – [StoreSalesPrevYr])/[StoreSalesPrevYr], BLANK())
Place [StoresSales] and [YOYGrowth] in a PivotTable with countries on rows, and years on columns:
There is one more set of Time Intelligence functions in DAX – these are the functions that evaluate an expression over a specified time period. These functions are all provided as a convenience. You can accomplish the same thing using CALCULATE and other Time Intelligence functions. For example,
= TOTALMTD (Expression, Date_Column [, SetFilter])
is precisely the same as this combination of functions:
= CALCULATE (Expression, DATESMTD (Date_Column)[, SetFilter])
But it will be simpler and easier for users to use these DAX functions when they are a good fit for the problem that needs to be solved. The DAX functions that do this are:
- TOTALMTD (Expression, Date_Column [, SetFilter])
- TOTALQTD (Expression, Date_Column [, SetFilter])
- TOTALYTD (Expression, Date_Column [, SetFilter] [,YE_Date])
For the functions that calculate opening and closing balances, there are certain concepts that are useful. First, as you might think obvious, the opening balance for any period is the same as the closing balance for the previous period. The closing balance includes all data through the end of the period, while the opening balance does not include any data from within the current period.
These functions always return the value of an expression evaluated for a specific point in time. The point in time we care about is always the last possible date value in a calendar period. The opening balance is based on the last date of the previous period, while the closing balance is based on the last date in the current period. The current period is always determined by the last date in the current date context.
- OPENINGBALANCEMONTH (Expression, Date_Column [,SetFilter])
- OPENINGBALANCEQUARTER Quarter (Expression, Date_Column [,SetFilter])
- OPENINGBALANCEYEAR (Expression, Date_Column [,SetFilter] [,YE_Date])
- CLOSINGBALANCEMONTH (Expression, Date_Column [,SetFilter])
- CLOSINGBALANCEQUARTER (Expression, Date_Column [,SetFilter])
- CLOSINGBALANCEYEAR (Expression, Date_Column [,SetFilter] [,YE_Date])
Calculating many time periods within a single measure formula
Consider the following eight calculations. Each of these formulas calculates a sales amount (in the Contoso database) for a distinct time period.
Current Period Last Year
Let’s create a new table named DimPeriod in PowerPivot that has a single column named Period, by pasting in a set of text strings like this from Excel:
Now let’s build a single measure formula that does the following:
1. Use an IF function, to see if the DimPeriod[Period] column has been placed into the Filter Context by placing this column onto the PivotTable’s column labels, or row labels, or perhaps on a slicer. The best way is to count the number of values that are applicable from this column. If this isn’t in the filter context, all 8 values will be applicable. But if this is on columns or rows, then only one of the values will be applicable for each cell in the values area (except for the Total row/column).
2. If only one of these values is applicable, then use nested IF statements to see which one it is, and apply the appropriate formula.
3. If this column is not on rows or columns (or selected to a single value in a slicer) then assume that the user wants Current sales figure instead of one of the more elaborate calculations.
Such a formula would look like this:
=IF( COUNTROWS( VALUES( DimPeriod[Period]))=1,
IF( VALUES( DimPeriod[Period]) = "Current", [Sales],
IF( VALUES( DimPeriod[Period]) = "MTD", [Sales](DATESMTD(DimDate[Datekey])),
IF( VALUES( DimPeriod[Period]) = "QTD", [Sales](DATESQTD(DimDate[Datekey])),
IF( VALUES( DimPeriod[Period]) = "YTD", [Sales](DATESYTD(DimDate[Datekey])),
IF( VALUES( DimPeriod[Period]) = "LastYear", [Sales](DATEADD(DimDate[Datekey],-1,YEAR)),
IF( VALUES( DimPeriod[Period]) = "PriorYearMTD", [Sales](DATEADD(DATESMTD(DimDate[Datekey]),-1,YEAR)),
IF( VALUES( DimPeriod[Period]) = "PriorYearQTD", [Sales](DATEADD(DATESQTD(DimDate[Datekey]),-1,YEAR)),
IF( VALUES( DimPeriod[Period]) = "PriorYearYTD", [Sales](DATEADD(DATESYTD(DimDate[Datekey]),-1,YEAR)),
Now if I build a PivotTable with DimGeography[RegionCountryName] on row labels, DimPeriod[Period] on column labels, a single date from DimDate[FullDateLabel] in the Report Filter, and my new formula as the only measure, I’ll get a PivotTable that looks like this:
This is the sort of analysis that is incredibly compelling for end users who want to see their measure for a variety of time periods. Of course there are many more calculations that are possible – this is simply meant as an illustration of how to place various time periods on the columns of a PivotTable.
I hope you enjoy the DAX Time Intelligence functions!