Tutorial: Sales analysis, part 5 - Time Intelligence functions
In an earlier part of our series looking at new tools for working with accounting data in Excel 2010 and 2013, we looked at some of the DAX functions available when using the Power Pivot add-in that relate to dates and time.
We also mentioned the need to work with a special table containing all the possible dates. This time, we'll consider some more of the date and time functions and also explain, in more detail, why we need the separate table of dates.
As in the previous article, we will use the Invoices data from Microsoft’s sample database: Northwind. (Our thanks to Microsoft for permission to use it in this tutorial. If you are not able to locate a copy of the 434kb zipped you can to download a copy here, then extract Northwind.mdb).
Time Intelligence DAX functions
Particularly when working with financial data, the ability to compare results across different periods is vital. There are more than 30 DAX functions that work with date-related logic.
Most of them return a date or a table of dates and, as such, are usually used as filters in DAX expressions used to create Measures (Excel 2010) or Calculated Fields (Excel 2013) in our PivotTable.
For our example, we will use the function we used in the previous article: SAMEPERIODLASTYEAR(). This function takes as its argument a column (or single column table) of dates. It returns the same table of dates, tardissed back a year.
To try and explain, not only SAMEPERIODLASTYEAR(), but also the time intelligence functions in general, we'll use a very simple table with just four entries:
As you can see, we have two entries per year for 2012 and 2013. Of our two 2013 entries one (01/01/13) has an entry for the same day in the previous year and the other doesn't.
We will add a Calculated Field. In Excel 2013 we can do this via: PowerPivot ribbon tab, Calculations group, Calculated Fields, New Calculated Field. In Excel 2010 the equivalent option is: PowerPivot ribbon tab, Measures group, New Measure:
Industry insightsView more
Our field totals all the [Value] amounts in Table1 subject to a filter. The filter being all the [Date] values in Table1, set back by one year.
As we mentioned last time, you can think of every cell in a PivotTable as being filtered by a series of criteria. These criteria are:
- Any explicit filters or Sliders set for the PivotTable
- Row values for that cell
- Column values for that cell
Here we have a very simple PivotTable based on our very simple Table:
We have no explicit PivotTable filters and no column filters. We just have row values based on the year, month and day of the Date field. Looking at cell E2, our 'table' is filtered by the year=2012. For E6, for example, our table is filtered by date=03/02/13.
Let's add our calculated field and see what happens:
Thinking in terms of tables and filters should explain the result. The only cell that returns a value is F7. Because row 7 is the totals row, it is not filtered. For all the other values the 'table' passed through to the SAMEPERIODLASTYEAR() function contains the dates in our table, filtered by the row value. This being the case, why don't we see a value for the 2013, the month 2 and particularly the 01/02/2013 row? All of these do have corresponding values for the same period last year.
Our problem is that our values are being filtered twice. First of all, we have the obvious row filters that we have discussed, but our Calculated Field applies an additional filter – the same date as our row filter but back a year. For an entry to pass both filters it needs to be in both 2013 and 2012 which is impossible, hence all the filtered rows are blank.
We'll try using the ALL() DAX function to cancel any filters applied by the PivotTable itself:
By overriding the PivotTable filters using ALL(), our 'Last year – all' column is now just filtered by the same period last year dates. For G3, the filter is all the dates in our table that fall in 2013, set back a year to 2012. Two dates in our table fall into 2013, so our table is filtered by both those dates back a year:
Filtering our table by these two dates will return the value of 20 that we see. There is no 30/01/13 in our table to be set back a year to match our 30/01/12 value.
The same logic applies to the values in G4 and G5
Separate dates table
As indicated earlier, the solution to this problem is to create a separate table containing all the dates covered by our data. We have created a simple Excel Table by dragging the first date in our period down to the last possible date. We have added this table to our PowerPivot data model and added columns to group by Year and Month:
We can now link our new DimDate table to our transaction Table. Using View, Diagram View we can see our tables and fields and drag from Date to Date to join them:
We now need to add another Measure/Calculated Field based on our DimDate table:
If we add this field to our existing table, we still won't get the result we want:
We need to recreate our PivotTable using the Date, Year and Month fields from the dimDates table of all dates rather than the equivalent fields from the transaction table as shown in the orange PivotTable below:
This may or may not sound complicated and difficult but, as long as you appreciate the need for the separate date table and the way that it is used, you should be able to apply the same method to using many of the other DAX time intelligence functions. Here is an example using TOTALYTD(), TOTALQTD(), TOTALMTD() and PREVIOUSMONTH() with our Northwind sales data:
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.