Create Time Dimension

Overview

Create Time Dimension App Logo

The Create Time Dimension App for Excel 2013 is an Office app available from the Microsoft Office App store that will help design and insert a table of time dimension data into the Excel spreadsheet.

This time information can be used for further analysis by mapping it to one or more data tables of facts in PowerPivot. Once the table has been created in Excel it can be further enhanced by adding additional columns with Excel formulas or it can be linked to the PowerPivot Data Model and further enhanced with DAX formulas.

By linking one of the date columns to the facts date column in the different facts tables in the PowerPivot Data Model it is possible to do date based analysis with ease. By creating hierarchies in PowerPivot it is easy to visualize data in hierarchies in a Pivot Table in Excel. The date information can also be used for timelines and date based slicers in Power View.

Screenshots

org_1_512

App start, choose start and end date and your choice of options.

org_2_512

Click insert and the table will be created in the Excel spreadsheet.

Once the table is available you can use it in your PowerPivot Data Model. More information on how to do this is available on this page.

Options

There are some settings available in the App that controls what and how the table is inserted.

Start and End Date: Choose the start date and end date for the table. The App will create rows for each day for the time span between the dates, including the start and end dates. If the start date is after the end date no rows will be included in the table. The App will default to January first two years back for start date and last of December next year for end date.

Language: Choose the language used for the table headers and month/day names. if you miss a language and can help with a translation, please feel free to send me the translations and I’ll include it in a future update.

Week Starts On: choose the day to be used as the first day of week. This can be either Sunday (common in USA) or Monday (common in Europe).

Week Number System: ISO-8601 based week numbers. Note that this is a first release of week numbers. It supports calculation of the standard ISO-8601 based week number. This defines that weeks start on Monday and is most commonly used outside of the US. A wider range of week number formats are planned. if you would like to use another week number format today you can always add a column to the table manually and use the =WEEKNUM(date, format) formula with the format of your choice.

Id column starts from: The table includes an id number column consisting of an integer value. The start value can be customized if there is a date id field instead of a date field in a fact table where a relationship is needed.

More Resources:

External Resources

  • This App only works in Excel 2013.
  • This App creates a Time Dimension table that is most useful in PowerPivot.