Understand and create date tables in Power Pivot in Excel

Create a date table in Excel

You can create a date table in Excel and then copy it into a new table in the Data Model. This is really quite easy to do and it gives you a lot of flexibility.

When you create a date table in Excel, you begin with a single column with a contiguous range of dates. You can then create additional columns such as Year, Quarter, Month, Fiscal Year, Period, etc. in the Excel worksheet by using Excel formulas, or, after you copy the table into the Data Model, you can create them as calculated columns. Creating additional date columns in Power Pivot is described in the Adding New Date Columns to the Date Table section later in this article.

Adding new date columns to the date table

A date table with a single date column that has one row for every day for each year is important for defining all of the dates in a date range. It’s also necessary for creating a relationship between the fact table and the date table. But that single date column with one row for every day is not useful when analyzing by dates in a PivotTable or Power View report. You want your date table to include columns that help you aggregate your data for a range or group of dates. For example, you might want to sum sales amounts by month or quarter, or you may create a measure that calculates year-over-year growth. In each of these cases, your date table needs year, month, or quarter columns that allow you to aggregate your data for that period.

If you imported your date table from a relational data source, it may already include the different types of date columns you want. In some cases, you may want to modify some of those columns or create additional date columns. This is especially true if you create your own date table in Excel and copy it into the Data Model. Fortunately, creating new date columns in Power Pivot is quite easy with Date and Time Functions in DAX.

DAX Date and Time Functions

If you’ve ever worked with date and time functions in Excel formulas, then you will likely be familiar with the Date and Time Functions. Though these functions are similar to their counterparts in Excel, there are some important differences:

• DAX Date and Time functions use a datetime data type.

• They can take values from a column as an argument.

• They can be used to return and/or manipulate date values.

These functions are often used when creating custom date columns in a date table, so they are important to understand. We will use a number of these functions to create columns for Year, Quarter, FiscalMonth, and so on.

DAX includes the following Date and Time functions:

• DATE

• DATEVALUE

• NEXTDAY

• EDATE

• EOMONTH

• HOUR

• MINUTE

• MONTH

• NOW

• SECOND

• TIME

• TIMEVALUE

• TODAY

• WEEKDAY

• WEEKNUM

• YEAR

• YEARFRAC

There are many other DAX functions you can use in your formulas, too. For example, many of the formulas described here use Math and Trigonometric Functions like MOD and TRUNC, Logical Functions like IF, and Text Functions like FORMAT For more information about other DAX functions, see the Additional Resources section later in this article.

Formula examples for a calendar year

The following examples describe formulas used to create additional columns in a date table named Calendar. One column, named Date, already exists and contains a contiguous range of dates from 1/1/2010 through 12/31/2016.

Month Name

=FORMAT([date],"mmmm")

In this formula, to get the month name, we use the FORMAT function to convert a numeric value from the Date column to text. We specify the Date column as the first argument, and then the format; we want our month name to show all characters, so we use “mmmm”. Our result looks like this:

If we want to return the month name abbreviated to three letters, we would use “mmm” in the format argument.

Day of Week

=FORMAT([date],"ddd")

In this formula, we use the FORMAT function to get the day name. Because we just want an abbreviated day name, we specify “ddd” in the format argument.

Sample PivotTable

Once you have fields for dates such as Year, Quarter, Month, etc., you can use them in a PivotTable or report. For example, the following image shows the SalesAmount field from the Sales fact table in VALUES, and Year and Quarter from the Calendar dimension table in ROWS. SalesAmount is aggregated for year and quarter context.

Formula examples for a fiscal year

Holidays or special dates

You may want to include a date column that indicates certain dates are holidays or some other special date. For example, you may want to sum sales totals for New Years day by adding a Holiday field to a PivotTable, as a slicer, or filter. In other cases you may want to exclude those dates from other date columns or in a measure.

Including holidays or special days is quite simple. You can create a table in Excel that has the dates you want to include. You can then copy or use Add to Data Model to add it to the Data Model as a linked table. In most cases, it isn’t necessary to create a relationship between the table and the Calendar table. Any formulas referencing it can use the LOOKUPVALUE function to return values.

Below is an example of a table created in Excel that includes holidays to be added to the date table:

In the date table, we create a column named Holiday and use a formula like this:

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

Let’s look at this formula more carefully.

We use the LOOKUPVALUE function to get values from the Holiday column in the Holidays table. In the first argument, we specify the column where our result value will be. We specify the Holiday column in the Holidays table because that is the value we want returned.

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

We then specify the second argument, the search column that has the dates we want to search for. We specify the Date column in the Holidays table, like this:

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

Finally, we specify the column in our Calendar table that has the dates we want to search for in the Holiday table. This of course is the Date column in the Calendar table.

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

The Holiday column will return the holiday name for each row that has a date value that matches a date in the Holidays table.

Custom calendar - thirteen four-week periods

Some organizations, like retail or food service, often report on different periods, like thirteen four-week periods. With a thirteen four-week period calendar, each period is 28 days; therefore, each period contains four Mondays, four Tuesdays, four Wednesdays, and so on. Each period contains the same number of days, and typically, holidays will fall within the same period each year. You can choose to start a period on any day of the week. Just like with dates in a calendar or fiscal year, you can use DAX to create additional columns with custom dates.

In the examples below, the first full period starts on the first Sunday of the fiscal year. In this case, the fiscal year begins on 7/1.

Period

The period in this custom calendar contains 28 days and it will always begin on a Sunday. This column will return the number of the period beginning with the first Sunday in the first fiscal year.

=INT(([Week]+3)/4)

Let’s look at this formula more carefully.

First, we create a formula that returns a value from the Week column as an integer, like this:

=INT([Week])

Then add 3 to that value, like this:

=INT([Week]+3)

Then divide the result by 4, like this:

=INT(([Week]+3)/4)

The result looks like this:

Period Fiscal Year

This value returns the fiscal year for a period.

=INT(([Period]+12)/13)+2008

Let’s look at this formula more carefully.

First, we create a formula that returns a value from Period and adds 12:

= ([Period]+12)

We divide the result by 13, because there are thirteen 28 day periods in the fiscal year:

=(([Period]+12)/13)

We add 2010, because that is the first year in the table:

=(([Period]+12)/13)+2010

Finally we use the INT function to remove any fraction of the result, and return a whole number, when divided by 13, like this:

=INT(([Period]+12)/13)+2010

The result looks like this:

Period in FiscalYear

This value returns the period number, 1 – 13, starting with the first full Period (beginning on Sunday) in each fiscal year.

=IF(MOD([Period],13), MOD([Period],13),13)

This formula is a little more complex, so we will describe it first in a language we better understand. This formula states, divide the value from [Period] by 13 to get a period number (1-13) in the year. If that number is 0, then return 13.

First, we create a formula that returns the remainder of the value from Period by 13. We can use the MOD (Math and Trigonometric functions) like this:

=MOD([Period],13)

This, for the most part, gives us the result we want, except where the value for Period is 0 because those dates don’t fall within the first fiscal year, like in the first five days of our example Calendar date table. We can take care of this with an IF function. In case our result is 0, we return 13, like this:

=IF(MOD([Period],13),MOD([Period],13),13)

The result looks like this:

Multiple relationships

In some cases, it may be necessary to create multiple relationships or create multiple date tables. For example, if there are multiple date fields in the Sales fact table, such as DateKey, ShipDate, and ReturnDate, they can all have relationships to the Date field in the Calendar date table, but only one of those can be an active relationship. In this case, because DateKey represents the date of the transaction, and therefore the most important date, this would best serve as the active relationship. The others have inactive relationships.

The following PivotTable calculates total sales by Fiscal Year and Fiscal Quarter. A measure named Total Sales, with the formula Total Sales:=SUM([SalesAmount]), is placed in VALUES, and FiscalYear and FiscalQuarter fields from the Calendar date table are placed in ROWS.

This straight-forward PivotTable works correctly because we want to sum our total sales by the transactiondate in DateKey. Our Total Sales measure uses the dates in DateKey and is summed by fiscal year and fiscal quarter because there is a relationship between DateKey in the Sales table and the Date column in the Calendar date table.

Inactive relationships

But, what if we wanted to sum our total sales not by transaction date, but by ship date? We need a relationship between the ShipDate column in the Sales table and the Date column in the Calendar table. If we don’t create that relationship, our aggregations are always based on the transaction date. However, we can have multiple relationships, even though only one can be active, and because transaction date is the most important, it gets the active relationship with the Calendar table.

In this case, ShipDate has an inactive relationship, so any measure formula created to aggregate data based on ship dates must specify the inactive relationship by using the USERELATIONSHIP function.

For example, because there is an inactive relationship between the ShipDate column in the Sales table and the Date column in the Calendar table, we can create a measure that sums total sales by ship date. We use a formula like this to specify the relationship to use:

Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

This formula simply states: Calculate a sum for SalesAmount, but filter by using the relationship between the ShipDate column in the Sales table and the Date column in the Calendar table.

Now, if we create a PivotTable and put Total Sales by Ship Date measure in VALUES, and Fiscal Year and Fiscal Quarter on ROWS, we see the same Grand Total, but all other sum amounts for fiscal year and fiscal quarter are different because they are based on the ship date and not the transaction date.

Using inactive relationships allows you to use only one date table, but it does require that any measures (like Total Sales by Ship Date), reference the inactive relationship in its formula. There is another alternative, that is, use multiple date tables.

Multiple date tables

Another way to work with multiple date columns in your fact table is to create multiple date tables, and create separate active relationships between them. Let’s look at our Sales table example again. We have three columns with dates we might want to aggregate data on:

• A DateKey with the date of sale for each transaction.

• A ShipDate – with the date and time when the items sold were shipped to the customer.

• A ReturnDate – with the date and time when one or more item returned was received.

Remember, the DateKey field with the transaction date is most important. We will do most of our aggregations based on these dates, so we will most certainly want a relationship between it and the Date column in the Calendar table. If we don’t want to create inactive relationships between ShipDate and ReturnDate and the Date field in the Calendar table, thus requiring special measure formulas, we can create additional date tables for ship date and return date. We can then create active relationships between them.

In this example, we’ve created another date table named ShipCalendar. This, of course, also means creating additional date columns, and because these date columns are in a different date table, we want to name them in way that differentiates them from the same columns in the Calendar table. For example, we’ve created columns named ShipYear, ShipMonth, ShipQuarter, and so on.

If we create our PivotTable and put our Total Sales measure in VALUES, and ShipFiscalYear and ShipFiscalQuarter on ROWS, we see the same results that we saw when we created an inactive relationship and a special Total Sales by Ship Date calculated field.

Each of these approaches requires careful consideration. When using multiple relationships with a single date table, you may have to create special measures that transit inactive relationships by using the USERELATIONSHIP function. On the other hand, creating multiple date tables can be confusing in a Field List, and because you have more tables in the Data Model, it will require more memory. Experiment with what works best for you.

How to: Set the Date Table property

1. In the PowerPivot window, select the Calendar table.

2. On the Design tab, click Mark as date Table.

3. In the Mark as Date Table dialog box, select a column with unique values and the Date data type.

Converting text data type dates to a date data type

In some cases, a fact table with transaction data might contain dates of text data type. That is, a date that appears as 2012-12-04T11:47:09 is in fact not a date at all, or at least not the type of date Power Pivot can understand. It’s really just text that reads like a date. In order to create a relationship between a date column in the fact table, and a date column in a date table, both columns must be of the Date data type.

Typically, when you try to change the data type for a column of dates that are text data type to a date data type, Power Pivot can interpret the dates and convert it to a true date data type automatically. If Power Pivot cannot do a data type conversion, you will get a type mismatch error.

You can, however, still convert the dates into a true date data type. You can create a new calculated column and use a DAX formula to parse the year, month, day, time, etc. from the text strings and then concatenate it back together in a way Power Pivot can read as a true date.

In this example, we’ve imported a fact table named Sales into Power Pivot. It contains a column named DateTime. Values appear like this:

If we look at Data Type in Formatting group Power Pivot’s Home tab, we see that it is Text data type.

We can’t create a relationship between the DateTime column and the Date column in our date table because the data types don’t match. If we try to change the data type to Date, we get a type mismatch error:

In this case, Power Pivot was unable to convert the data type from text to date. We can still use this column, but in order to get it into a true date data type, we need to create a new column that parses the text and re-creates it into a value Power Pivot can make a Date data type.

Remember, from the Working with time section earlier in this article; unless it is necessary your analysis be to a time-of-day level of precision, you should convert dates in your fact table to a day level of precision. With that in-mind, we want the values in our new column to be at the day level of precision (excluding time). We can both convert the values in the DateTime column to a date data type and remove the time level of precision with the following formula:

=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))

This gives us a new column (in this case, named Date). Power Pivot even detects the values to be dates and sets the data type automatically to Date.

If we want to preserve the time level of precision, we simply extend the formula to include the hours, minutes, and seconds.

=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +

TIME(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))

Now that we have a Date column of the Date data type, we can create a relationship between it and a date column in a date.

Additional resources

Dates in Power Pivot

Calculations in Power Pivot

QuickStart: Learn DAX Basics in 30 Minutes

Data Analysis Expressions Reference

DAX Resource Center