Problem definition

Take a quick moment to think about why these two Pivot Tables might be different and what the problem is with the week names (Note: both of the Pivots Tables are unfiltered).

Pivot Table A	Pivot Table B

The weeks in Pivot Table A are Sunday-based (i.e. Sunday through Saturday) whereas the weeks in Pivot Table B are Monday-based (i.e. Monday Through Sunday), but you wouldn't be able to guess this by just looking at the images, would you?

With regards to what's wrong with the week names, the truth is, nothing is actually wrong from a logical or performance perspective. The weeks appear in the correct order and each week belongs to a single year. In other words, this is a natural hierarchy and follows the best practices mentioned in this article by Marco Russo. So what's the issue then? You'll quickly learn the answer to this by trying to answer the following question: Without drilling down, would you be able to quickly tell the starting date of 'Week 11 2006' in either of the Pivot Tables? Your answer would most likely be "no" and that's the problem. Unsurprisingly, most end-users wouldn't know the answer to this question either and that's an even bigger problem!

Drilling down is great and everything, but this is one extra step that the end-user has to take to gain a better understanding of what they are looking at. Imagine that the end-user isn't sure which week contains a particular date. They may end up trying to guess this by drilling down on the most likely weeks until they find it, or they may end up expanding all of the weeks at once, but neither of these actions is ideal.

Right, so now that we know what the problem is, let's move on to a possible solution…

Solution

You can add a friendly week name column to your date table with some DAX. Depending on your end-users you will need to create either Sunday-based weeks or Monday-based weeks. In some cases, your target audience may require the option of both. Let's assume that the latter is true for our examples.

A calculated column for 'Sunday Based Week' can be defined as:

A calculated column for 'Monday Based Week' can be defined as:

Notice that the difference between these two DAX formulas is the argument used for the second parameter of the WEEKNUM function. A '1' means that the starting date is a Sunday, and a '2' means that the starting date is a Monday. The first parameter must reference a column with the Date data type. You can learn about the WEEKNUM function in an official Microsoft Office help topic here.

We also need columns that can be used to sort the 'Sunday Based Week' and 'Monday Based Week' columns into the correct order. These same sorting columns will also be used to sort the friendly versions of the week names.

A calculated column for 'Sunday Based Week Number' can be defined as:

A calculated column for 'Monday Based Week Number' can be defined as: