THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

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.

Published Tuesday, June 29, 2010 6:13 PM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement