Compare Week Last Year for a 53 Week Year Using MDX

Many companies use 4-4-5 calendar which ends up with an extra week approximately every 5 years.  This makes comparing periods challenging when you encounter the 53 week year.  This is especially challenging when reports are driven from an SSAS cube data source, and you want to build in the logic to automatically compare the correct This Year period with the corresponding Last Year period as per the companies business rules.  There is a way to do this.  It isn’t pretty, but it works.  The reporting will automatically choose the correct matching period in the context of a 52 or 53 week year.

There are some possibilities.  A company may want to compare the year after a 53 week year by shifting each week of the 53 week year by 1 week.  For example, if 2012 is the 53 week year then we want to compare:

Another possibility is that the company may want to compare the 53 week year by shifting the previous year weeks, or in some other way identify whether a week belongs to a 52 or a 53 week year for purposes of comparing periods.  This requires a bit more under the hood, but also doable. 

COMPARE THE YEAR AFTER A 53 WEEK YEAR WITH THE CORRESPONDING SHIFTED WEEKS FOR LAST YEAR

This is relatively easy.  Basically you make all your Last Year (LY) calculations and LY Period To Date calculations by counting back 52 weeks from the current week.  The week in your hierarchy must always be selected in your query.  I use Fiscal Week ID as the level to which I map all previous period calculations, so the Fiscal Week ID must be selected in all queries using these calculations.

This calculation will return the [Sls Dollars LY TW] (last year this week)

(PARALLELPERIOD
    ( [TIME].[Week Period To Date].[Fiscal Week ID], 52, [TIME].[Week Period To Date].CURRENTMEMBER  )
,[Measures].[Sales Dollars])

Notice how 2013 Wk 1 shows 2012 Wk 2 for the Sls Dollars LY TW.  And 2012 Wk 2 shows 2011 Wk 2 as desired.

For Periods To Date you can do this the same way.  You would like the LY QTD for 2013 Wk1 to show the QTD up to 2012 Wk 2.

This query returns [Sls Dollars LY QTD].

Aggregate
(
  PeriodsToDate
  (
            [TIME].[Week Period To Date].[Fiscal Quarter],

             PARALLELPERIOD
            ( [TIME].[Week Period To Date].[Fiscal Week ID],52,
                [TIME].[Week Period To Date].CURRENTMEMBER
            )
  ),
[Measures].[Sales Dollars]
)

Here are the results.  Again, you must have the Fiscal Week Id from the hierarchy selected in your query for this to work.

Notice how 2013 Wk 1 shows 2012 Wk 2 QTD for the Sls Dollars LY QTD. And 2012 Wk 2 shows 2011 Wk 2 QTD as desired.

IDENTIFY WHETHER A WEEK FALLS IN A 52 OR A 53 WEEK YEAR FOR USE IN CALCULATIONS

Here is an example of how to identify whether a week falls in a 52 or 53 week year, in order to be able to use in your calculations.  It isn’t pretty, but it does the job.

1.  Add a column to your DIM_Date table to identify whether a year has 52 or 53 weeks. 

2. Add a named calculation to the DIM_Date table in your cube Data Source View. 

The calculation is FiscalWeeksCount*10000+FiscalYearID. This will give you an integer which begins with either 52 or 53 and ends with the Year.  For example 522011 or 532012.

3. Add this calculated field to your Time dimension. Add it as a level of your Time Hierarchy, directly after Fiscal Year.  You may prefer to create a new hierarchy specifically for reporting needs so as not to confuse users. 

The reason the Year is appended to the Fiscal Weeks Count, rather than having the Fiscal Year roll directly up to Fiscal Weeks Count is that that would disturb the order of the children. 53 would come after 52 in the hierarchy, and therefor 2012 would come after 2013. The MDX calculations use relative position in the hierarchy to find a previous period, so the weeks must remain in the correct order in the hierarchy in order for this to work.

4. Save the change and rebuild the cube. Now you can reference this level in the hierarchy in order to identify how many weeks in the fiscal year.  You can use it in a case statement, like this:

CASE
WHEN [TIME].[Fiscal Weeks Count Year].CURRENTMEMBER.MEMBERVALUE > 530000 THEN
(PARALLELPERIOD ( [TIME].[Week Period To Date].[Fiscal Week ID], 53, [TIME].[Week Period To Date].CURRENTMEMBER ), [Measures].[Sales Dollars] )
ELSE
(PARALLELPERIOD ( [TIME].[Week Period To Date].[Fiscal Week ID], 52, [TIME].[Week Period To Date].CURRENTMEMBER ), [Measures].[Sales Dollars])
END

This determines if the current selected week falls in a 52 or a 53 week year, and calculated the corresponding previous period accordingly.  You could use ANCESTOR if you want to find out if the previous year was a 52 or a 53 week year.  You now know where your selected week is relative to a 53 week year and can use this information as needed.