ISO 8601 Week in DAX

I recently built a PowerPiovt model where I had to display weeks according to ISO 8601. As I came across this frequently in the past when I developed SQL Server databases (prior to 2008) I was aware that ISO weeks can also be calculated using some complex logics. When I discussed this with some of my colleagues during a training, one of the attendees told me, that this can be solved in Excel very easily using Excels WEEKNUM()-function. This function takes to arguments:

Serial_num is a date within the week. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Return_type is a number that determines on which day the week begins. The default is 1.

Return_type Week Begins
1 Week begins on Sunday. Weekdays are numbered 1 through 7.
2 Week begins on Monday. Weekdays are numbered 1 through 7.

According to Excels Online-Help the second parameter only supports values 1 and 2. But this is not 100% true. You can also use the value 21 as second parameter, and guess what – it now returns weeknumbers according to ISO 8601. As many DAX-functions are derived from Excel (including WEEKNUM()-function), this also works with DAX’s WEEKNUM()-function!

So by creating a calculated column as

you get the ISO week number for the current [Date].

If you also want to calculate “ISO years” to build clean hierarchies you may want to use this formula in an other calculated column:

 

I think we can learn a lot from experienced Excel-users here as most tricks also work in PowerPivot/DAX!

If you want to do the same in Power Query already, here is some sample code (original source)

Synchronizing PivotTable Filters without using VBA

I recently did a workshop with one of my customers where we were building a PowerPivot model which was later used for several reports. The final workbook contained 10+ worksheets each with one or more PivotTables querying the PowerPivot model. Most of the PivotTables had several filters in common. For example Year, Period, Organisation and so on. A very important requirement for the customer was that whenever one of those filters is changed, all other PivotTables in the workbook should also be synchronized and filtered accordingly.

A common solution for this problem is to use a VBA-Macro that pushes changes in the filter across all PivotTables. This approach is described in many blogs and/or forum entries:
http://www.excelforum.com/excel-programming/778048-synchronizing-two-pivot-tables-of-same-data-with-one-report-filter.html
http://www.ozgrid.com/forum/showthread.php?t=87519

This solutions works fine but has the same drawback as all other VBA Macros:

  • Users may need special security to use macros as they could be unsafe
  • Macros may be forbidden by your company
  • and (especially for BI) THEY DO NOT WORK WITH EXCEL SERVICES!!!

 

Also for my customer macros have not been an option. As I could not find any other solution in the web, I was very happy when one of my colleagues showed me an other option to solve this issue.
We did not use any macros but only used native, built-in excel functionalities – Slicers.

And this is how it works:

  1. Create a two PivotTables on top of your datasource (I tested multidimensional and tabular datasources, but I assume that this will also work for all other datasources)
  2. Add the same field(s) to the Report-Filter of your PivotTables
  3. Use rows, columns and values as you like
    In my example I used the Adventore Works cube and created the following worksheet:
    InitialPivotTables

I used [Date].[CalendarYear] in both PivotTables as a filter. As you already know these filters are independent of each other usually.

To solve the initial problem and keep them in sync you first have to add a new slicer also for [Date].[CalendarYear]

PivotTablesWithSlicer

and connect it to both PivotTables:

SlicerConnections

And that’s actually all you have to do!

Now you can either change the filter of PivotTable1, PivotTable2 or use the slicer to select your CalendarYear. The selection will be propagated through all objects that are connected to the slicer!

Another neat feature of slicers is, that the do not have to reside on the same worksheet as the connected PivotTables. So you could also move them to a hidden worksheet and everything will still work!

Also multiselects work just fine!

You may also want to put hierarchies into the filter – this also works
But be aware that one slicer is created for each hierarchy-level!

 

I have not tested this solution in Excel-Services and SharePoint yet but I assume that it will also work there. When I have some more time I will test this and post my results.

UPDATE: I just ran some tests on SharePoint and this solution also works with Excel-Services