Hide the Date column whenever you define a Dates table in PowerPivot

Posted on Jun 29, 2010

There are already severalposts in the blogosphere about the need of using a separate Dates table in PowerPivot to make almost any type of analysis. An important step you should make whenever you have a Dates table (almost always, in theory!) is to hide the Date column of the table containing measures in the Pivot Table.

For example, suppose you define these measures, having a Balances table and a Dates table in your PowerPivot model:

Now, let’s put some of these measures in a Pivot Table, putting Quarter and Month from Dates table in row. Everything is ok. Now, let’s put the Date column from the Balances table in rows – this is the result, and it’s something that seems crazy:

In reality, everything is good, because you are looking at the date to which corresponds the closing balance of the quarter, that might be different from the date you are trying to analyze. However, the picture above worth more than 1,000 words. Do you really think that it is intuitive? I don’t think so.

Thus, 99.99% of the times, it is better to avoid that type of visualization, hiding the Date column of a table containing measures whenever you have a related Dates table in your model. To do that, just click the Hide and Unhide button in PowerPivot as shown in the following picture.

I just spent the last weeks writing a whole chapter about calculation involving Dates of our next book about PowerPivot. Still some weeks of job and we’ll be done with the book, so I will return publishing some material that I accumulated in the meantime.

This article describes how DAX automatically converts data types in arithmetic operations. These small details can cause and explain differences in results when using the same operations in other languages. Read more

This article describes how to use the detail rows expression of a measure to obtain the equivalent of creating table functions in DAX. This allows the reusing of a table expression in multiple CALCULATE filters. Read more