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

There are already several posts 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:

ClosingBalanceMonth = CLOSINGBALANCEMONTH( SUM( Balances[Balance] ), BalanceDate[Date] )
ClosingBalanceQuarter = CLOSINGBALANCEQUARTER( SUM( Balances[Balance] ), BalanceDate[Date] )
ClosingBalanceYear = CLOSINGBALANCEYEAR( SUM( Balances[Balance] ), BalanceDate[Date] )

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.