Why use the PowerPivot Time intel golden rules? With examples

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:

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

So here is what happens when you:

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.