I have had a lot of question about time intelligent functions lately. Most folks ask me whether they really need to follow all the PowerPivot Time Intelligent Golden rules and what happens when they don’t.
In this blog I will show you what happens when you do not use all the rules, these are the rules you should take to hart:
- Never use the datetime column from the fact table in time functions.
- Always create a separate Time table.
- Make sure your datetime table has a continues date range
- Create relationships between fact tables and the Time table.
- Make sure that relationships are based on a datetime column (and NOT based on another artificial key column).
- The datetime column in the Time table should be at day granularity (without fractions of a day).
So here is what happens when you:
- Do not use a separate time table, this will give you unexpected behavior and results of time intel functions.
For example:
http://www.kasperonbi.com/ok-why-should-i-want-to-load-an-entire-time-table-in-powerpivot - Make sure that relationships are based on a datetime column
When you don’t use a relationship based on a datetime column you need to work around it using the All function
and this will happen:
read more: http://www.kasperonbi.com/powerpivot-time-intelligent-functions-revisited-why-use-all-and-how-to-work-around-it - You want to use a date column that is based on the day granularity or relationships won’t be recognized (date with hours won’t relate to a date with no hours)
http://www.kasperonbi.com/powerpivot-creating-a-clean-date-column-to-create-your-relationship-upon - Do not use a continues date range or PowerPivot functions will behave not as expected.
http://cwebbbi.wordpress.com/2010/06/03/the-%E2%80%98function-xxxx-only-works-with-contiguous-date-selections%E2%80%99-error/
As you can see making sure all Time Intel. golden rules are met is a very good idea, otherwise you will get surprising results or DAX functions that will return no values.
Well after building my first time function with the golden rules, I was immediately convinced. Before that I worked with artificial keys for the time dimension table, that was a drag, now everything works as aspected. Thanks!