Creating Friendly Week Names in Power Pivot and SSAS Tabular Models with DAX
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:
="Week " & WEEKNUM('Date'[Date], 1) & " " & 'Date'[Year]
A calculated column for 'Monday Based Week' can be defined as:
="Week " & WEEKNUM('Date'[Date], 2) & " " & 'Date'[Year]
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:
=VALUE('Date'[Year] & RIGHT("00" & WEEKNUM('Date'[Date], 1), 2))
A calculated column for 'Monday Based Week Number' can be defined as:
=VALUE('Date'[Year] & RIGHT("00" & WEEKNUM('Date'[Date], 2), 2))
Now let's move on to our friendly week name columns…
A calculated column for 'Friendly Sunday Based Week' can be defined as:
=
"Week "
& WEEKNUM('Date'[Date], 1)
& " ("
& FORMAT(
CALCULATE(
MIN('Date'[Date]),
ALLEXCEPT(
'Date',
'Date'[Sunday Based Week]
)
),
"ddd d, MMM yy"
)
& ")"
A calculated column for 'Friendly Monday Based Week' can be defined as:
=
"Week "
& WEEKNUM('Date'[Date], 2)
& " ("
& FORMAT(
CALCULATE(
MIN('Date'[Date]),
ALLEXCEPT(
'Date',
'Date'[Monday Based Week]
)
),
"ddd d, MMM yy"
)
& ")"
I will outline what's happening for the 'Friendly Sunday Based Week' calculated column but near-identical logic applies to the Monday-based week version. The DAX formula does the following things for each row:
- Clear all the filters from the filter context, that was derived from the row context, except for on the 'Sunday Based Week' column.
- Find the first/earliest date within this context.
- Format the date as ddd d, MMM yy e.g. 'Sun 1, Jan 06'.
- Determine the week number of the current row's 'Date' column using the WEEKNUM function.
- Build the text string 'Week <Week Number> (<Formatted Date>)' e.g. 'Week 1 (Sun 1, Jan 06)'.
Note: The exact formatting used for the friendly week names in the examples presented are to illustrate the approach, but it's likely that you may require much shorter friendly names so that they fit comfortably on your charts. You can use the FORMAT function and custom format strings to get the desired outcome such as 'Wk1 1-Jan-06' instead of 'Week 1 (Sun 1, Jan 06)'. Learn about the FORMAT function and custom format strings here and here. You may also notice that the first day for the first week will not always start on the corresponding Sunday or Monday. This is ok in this scenario because the friendly week name should only reflect the dates that would be implicitly filtered when selecting or slicing the data.
These two columns need to be sorted using the same respective sort columns as their 'non-friendly' week name counterparts and then we can use them to create friendly week name hierarchies.
 |
 |
| Pivot Table A | Pivot Table B |
Now that we have these friendly week names, you can easily tell why the numbers are different. Without drilling down, would you be able to quickly tell the starting date of 'Week 11 2006' in either of the Pivot Tables? The answer is most likely a "yes". If you needed to look at sales for the whole week containing the date 7th April 2006, would you be able to pick the correct week to drill-down on? The answer is probably "yes" to this question too!
Conclusion
Whenever creating calculated columns in our Power Pivot or SSAS Tabular models, if we expect that the column will ever be used as a label in Pivot Tables or charts, we should ensure that the values within it are unambiguous. Doing this will save end-users some time whilst also helping to avoid the confusion that often surrounds ambiguous labels in reports and charts.