One of the best things you can do as an analyst is add context to your numbers. As Edward Tufte put it, frame the problem with the question “Compared to what?” in mind.
Often in business settings we want to know about trends. How did sales do compared to last week? Last month? Or we may want to view the data during different time periods: what do things look like month to date? Year to date?
Making these comparisons and rollups in classic Excel can be a major headache. With Power Pivot it’s much easier, but for best effect you need to add a date or calendar table to your data model first. Let’s take a look at how to do that here.
Checking for the Power Pivot add-in
Before getting started, check to see if you have a Power Pivot tab on the ribbon of your Excel. If not, see how to turn it on here. Please note that Power Pivot is not available on every version of Excel.
Creating the date table
Next, go the Power Pivot tab of your starter workbook and select Manage Data Model.
Once inside, select Diagram View. You should now see the three tables of Superstore and their relationships in the data model:
You will see that this data model does include dates: the orders table does have an Order Date and a Ship Date column. This data model does not have a dedicated calendar or date table. To get the most of time intelligence in Power Pivot, we should add one.
Go to the Design tab of the ribbon, then Date Table > New:
Defining the relationship
You should now see a fourth table called Calendar in your data model. We are going to create a relationship between the Date in this column and Order Date in the orders table. To do this, drop-and-drag a line from one column to the other:
Nice work! Let’s confirm this calendar table is doing its thing by going to the Home tab of the Power Pivot Editor and selecting PivotTable. Start off that PivotTable by placing Date Hierarchy from your Calendar table into the Rows:
Next, go to the Power Pivot tab of the ribbon and select Measures > New Measure:
Creating a time intelligence measure
Power Pivot and Excel have no shortage of time intelligence functions: you can pull previous periods, periods-to-date, and more. Let’s start with a Year to Date Sales calculation. From inside the measure calculation area, call your new measure Year to Date sales.
You can create this measure using the following TOTALYTD() formula:
=TOTALYTD(sum(orders[Sales]), 'Calendar'[Date])If you want to double-check your work, you can click “Check formula” here; you should see a “No errors in formula” message appear below your formula box.
For good measure (no pun intended), you can also format Year to Date sales by going to Formatting Options at the bottom of this menu. Select Currency, $ as symbol and 0 decimal places. Your measure input should look like this:
Exploring the time intelligence measure
Click OK. Year to Date sales should now be loaded into the Values area of your PivotTable. If it is not, you can drag it in from the Fields menu.
Go ahead and click the plus sign + next to the first year of the data 2014. You will see how the Year to Date Sales figure does increase through each month of the year, as expected:
Recap
Year-to-date and related time intelligence functions are one of the most powerful features of Power Pivot, but make sure to add a calendar/date table for best effect.
Download the completed exercise file here:
How have year-to-date or other time intelligence features in Power Pivot helped you? What questions do you have about Power Pivot? Let me know in the comments.
Leave a Reply