Kasper de Jonge PowerPivot Blog

I was helping a client this week with a quickstart on PowerPivot this week. She was working with datetime column in the fact table, relating this column to a values in a date table. When i used the relationship in the workbook just a few rows had actual relationships with the date column. It appeared the datetime in the fact table had times as well and that was why we the relationship couldn’t be made.

That night i remembered receiving a mail from Marius Dumitru from MSFT with a few rules on working with Time intelligent function in RTM PowerPivot.

When you apply these rules to your time intelligent functions are good to go:

1. Never use the datetime column from the fact table in time functions.
2. Always create a separate Time table.
3. Create relationships between fact tables and the Time table.
4. Make sure that relationships are based on a datetime column (and NOT based on another artificial key column).
5. The datetime column in the Time table should be at day granularity (without fractions of a day).

Applying item #4 above is what ensures that ALL() isn’t required at the end of calls to time functions.

Make sure you check out my other blog posts on Time intelligent functions as well:

PowerPivot time intelligent functions revisited: why use ALL() and how to work around it
Create a separate related time table for time intelligent functions in PowerPivot
Screencast: Introduction to time intelligent functions in PowerPivot

and MSDN blog post: Time Intelligence Functions in DAX