Microsoft Office Tutorials and References
In Depth Information
calculation. You might have no sales for a day (in fact, it is pretty common to have no sales
on nonworking days), so the separate Dates table allows you to make the right calculations
without requiring any modification of the original table that contains measures to analyze.
The only side effect of this technique is that you need to create a Dates table in PowerPivot
for each date attribute you want to analyze in a single table because there can be only one
relationship between two tables in PowerPivot.
Tip Creating multiple relationships with the same lookup table is not supported in PowerPivot. For
this reason, you must duplicate the Dates table whenever you have more date columns that you
want to analyze in the same table, such as Order Date and Ship Date attributes in a Sales table.
How to Build a Dates Table
To create a Dates table in PowerPivot, you need a data source that contains at least a column
with all days included in the period of time you want to analyze. For example, if the minimum
and maximum date contained in Sales data is July 3, 2001 and July 27, 2004, respectively, the
range of dates you should consider is between January 1, 2001 and December 31, 2004. In
this way, you have all the days for all the years containing sales data.
In Chapter 3, "Introduction to DAX," you saw how to create Day, Month, and Year calculated
columns for a Calendar table that has just the Date column as existing data. However, if you
do not have an external source providing you with a valid Dates table (such as a
corresponding table in SQL Server), we suggest that you create all the calculated columns for a Dates
table in Excel. In this way, it will be easier to copy and paste the entire contents of that table
into a new one when you have to handle more dates—for example, Order Date and Ship
Date—in your PowerPivot model.
To create your Dates table, you can start by typing Date in a cell and 1/1/2001 in the cell
below it, as you can see in Figure 7-2.
FIguRE 7-2 Creating a Dates table in Excel.