Dates in PowerPivot

Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.

This section describes some best practices for importing data that contains dates, and for working with dates in relationships and in PivotTables.

Best Practices for Importing Date Data

When you import data that has date/time data, especially from multiple sources, very often the data might contain dates in different formats or with different levels of granularity in the dates.

For example, Excel provides two functions for returning the current date/time: the TODAY function returns the same date as the NOW function, but the TODAY function always returns the default time of 12 AM, whereas the NOW function returns a precise time.

The problem with this is that the additional time information can cause the values to not match. Then, when you try to sum values in a PivotTable, you might find that the values cannot be grouped the way that you expect.

In addition to dates with different levels of precision, your data sources might include dates written in various text formats, dates in different regional formats, or dates based on different fiscal years.

To work with and integrate these different types of dates in a single model, after you import the data, you should:

• Use DAX formulas in calculated columns to truncate values, or to create a consistent set of date/time values.

• Create a master date/time table that you can use to create relationships between date columns.

The following sections provide detailed information.

Determine requirements for dates.

Before changing any of the date values, think about the analysis you plan to do using the data, and ask these questions:

• What level or levels of granularity do I count or group numeric facts by -- days, hours, weeks, quarters?

• What levels of granularity will be used to group dates --weeks, fiscal quarters, etc.?

• Are any dates missing? Is it acceptable for dates or other values to be missing, or do you need to insert placeholder values or dates? If values are missing do you use a zero or some other value chosen to represent unknown values?

Use formulas to convert dates to a consistent date format.

If the data that you imported contains dates in multiple formats, you can leave the columns as is, and use DAX formulas to create calculated columns that represent the dates in the correct format and at the specified level of granularity.

For examples, see the following topics:

DATE Function

DATEVALUE Function

• Use a DAX function to extract values such as the day, year, and month, if needed.

• Use DAX functions to compose values into a date/time format.

If the date values are not formatted as dates, or have an inconsistent format, you can use the date and time functions provided in DAX to build valid dates.

• Use the FORMAT function to work with custom numeric or date/time formats.

For a complete list of date and time functions, see DAX Function Reference.

Truncate times if you need to work with days, weeks, and months.

Days are the smallest unit of time that DAX time intelligence functions can work with. Therefore, if you do not need to work with time values, you should reduce the granularity of your data to use days as the minimum unit.

To resolve problems with unnecessarily precise time values, there are a couple of things you can do:

• Truncate times from date/time values, or make all date/time values use the same default time value.

• If you need to work with times --such as, hours, minutes, and seconds --create a separate field or fields that represent the time increments, by using calculated columns. Then you can analyze times separately.

The date/time data type that is used in PowerPivot is a SQL Server data type and by default creates a time value for each date.

Filter data at import to remove bad data.

If the external data contains invalid values, you can filter out the invalid data at import time. For more information, see the following topics:

Add Data by Using the Table Import Wizard (Tutorial)

Change the Rows that Are Imported from a Data Source

Best Practices for Working with Dates in PivotTables

This section provides some tips for helping you work with dates in PivotTables and formulas that use DAX time intelligence functions.

Avoid use of integer surrogate keys in relationships.

When you import external data from a relational data source, very often date and time columns are represented by a surrogate key, which is an integer column used to represent a unique date. However, in a PowerPivot workbook, you should avoid creating relationships by using integer date/time keys, and instead, for your keys, use columns that contain unique values with a date data type.

Although the use of surrogate keys is considered a best practice in traditional data warehouses, the integer keys are not needed in PowerPivot and can make it difficult to group values in PivotTables by different date periods.

Create a master table of dates.

If each table of data in your workbook contains a column of date/time values, and you connect the tables on those date/time columns, it is likely that many values will not match: for example, your Sales table might have dates for only the latter half of 2008, whereas your Suppliers table might list dates from 2006-2008.

Rather than joining various tables of data on many independent columns containing various dates and times, you can achieve better results by creating a master table that stores only date information. You can then link that table, using relationships, to your data tables, and reap the benefits of having a consistent set of dates to work with.

The DAX Samples workbook provides an example of a master date/time table that is connected to other tables using relationships.

In addition to having a unique date/time value for each date that you would use, the master table contains hierarchies that you can use for grouping dates in PivotTables, as shown in this table:

Create copies of date columns when necessary

The concept of a master date table will be familiar to you if you have worked with traditional Analysis Services databases, which use a date dimension to represent and group dates.

What is different in PowerPivot is that that each unique column in a PowerPivot table can participate in only one relationship between any two tables. Therefore, if a single table contains multiple columns that must be related to the date key, you must create a copy of the date key column and link.

For example, suppose your Orders table contains these date columns: SalesDate, TransactionDate, and ShippingDate. You want to link all of them to the date key column in the master dates table, but PowerPivot does not allow this, to ensure that any relationship provides a unique, unambiguous path though the values. Instead, you need to move the extra date columns into separate tables, and then link the date column in each of those tables to the date key in the master dates table. For example, you might decide to keep the SalesDate column in your Orders table, but create a new table for transactions, and a separate table for shipping information. You can use a calculated column to create the copies of the columns, ShippingDate and TransactionDate, to make sure they stay in synch.

The DAX Samples workbook provides an example of how to create and effectively work with copies of date columns. For more information on where to obtain the samples, see Get Sample Data for PowerPivot.

See Also

Concepts

Data Analysis Expressions (DAX) Overview

Other Resources

Prepare Data for Analysis in PowerPivot