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.

When dealing with report parameters that involve time, I usually like to default the report to the current unit of time used by the report, often month. When a user views the report in a particular month the parameter will always default to that month, saving the user from having to select it every time.

There are two elements to making this happen. One is to define the current month in your Time dimension, and the other is to build the dataset into your report to feed the default for the Time parameters.

1. Define current month in the SSAS Time dimension

I know there is Time functionality built into SSAS cubes, but I still like the flexibility of building attributes into my DIM_Date table in the data warehouse. In this way it can be used in both cube and SQL queries alike. I won’t go into details about how the Dim_Date table is created as that is another subject. The DIM_Date table is rebuilt nightly and an attribute called MonthPeriod identifies whether the date is in Current Month, Previous Month, etc. You can label any months you like, with your own descriptions. Some examples are below.

The important thing about this attribute is to ensure that MonthPeriodID for the current month is always 0, and the MonthPeriodID for other months is relative to that. So ‘Current Month –1’ is 1, ‘Current Month –2’ will be 2, etc. This way you can build logic into your reports which relies on the fact that, for example, last month has a MonthPeriodID of 1. It is critically important that the underlying ids belonging to each description are always the same. If Current Month is 0 today and changes to 10 tomorrow, the defaults in your reports will not work. SSRS stores the MDX tuple associated with the description and not the actual description. So when you select ‘Current Month’ in step 3 below, SSRS is storing [TIME].[Month Period].&[0] and not the ‘Current Month’ label.

Once you have this attribute defined in your DIM_Date table and it is being updated nightly, add it to the SSAS cube for use in your reports.

2. Build the default dataset into your report to feed the SSRS Parameter.

Background:

When you build the main dataset for the report and add parameters in your dataset,

SSRS automatically creates the dataset for the Available Values for the parameter. You can view these hidden datasets used to feed Available Values by right clicking on [Datasets] in the [Report Data] window in SSRS and selecting [Show Hidden Datasets].

You can view and/or edit any of these hidden datasets.

I don’t recommend editing the hidden datasets, since they are created and used by SSRS by default and your changes can sometimes be overwritten if you add additional datasets using the same parameters. If you wish to customize the Available Values you are better off creating a new dataset with a different name specific to that purpose and then changing Report Parameter Properties to use the new dataset to retrieve the Available Values.

Building the Default Dataset

For this particular report the user can choose Year and then Month Of Year. The same dataset can be used to feed both the default current year and the current month.

1. Right click on Datasets in the Report Data window and select Add Dataset.

2. Select the Radio Button ‘Use a dataset embedded in my report’. I like to use Shared Datasets for this type of thing, but for some reason shared datasets used for defaults with an SSAS data source don’t seem to work when published to SharePoint. So for now just embed it in the report. Select (or define) the cube you are using as your Data source, and click on the Query Designer button.

3. From the TIME dimension drag the ‘Month Period’ attribute defined in Part 1 to the filter area of the Query Designer. Click in the Filter Expression and select ‘Current Month’. Click OK.

4. Drag Year and Month of Year onto the Query design page. Right click on the design page and select ‘Include Empty Cells’ so the current attributes will appear when the query is executed.

5. Right click in the Calculated Members area and select ‘New Calculated Member’. From the Metadata pane drag the Year attribute from the TIME dimension into the Expression pane. Add to the end of the expression “.CurrentMember.UniqueName”. Give the Calculated Member a name of ‘YearValue’. Click OK.

6. Right click on the new calculated member called YearValue and select Add to Query.

7. Add another calculated member following steps 5 & 6 called MonthOfYearValue.

8. Your dataset now contains the Label and the underlying MDX tuple needed to feed the default time values to your report parameter.

9. Click OK to close out of the Query Designer. Give the dataset a name of DefaultYearMonth. Click OK to save the dataset.

10. Double click the TIMEYear parameter in the Paramaters folder of the Report Data window to edit the Report Parameter Properties. Select the Default Values option. Choose the ‘Get values from a query’ radio button. In the dataset drop down choose the DefaultYearMonth dataset you created. In the Value field dropdown select YearValue.

11. Do the same as Step 10 for the TIMEMonthOfYear parameter. Select DefaultYearMonth as the dataset, and MonthOfYearValue for the Value field.

When you preview your report, the parameters will now default to the current year and month. Going forward the default for the parameter will roll over to whichever month is current. Users appreciate not having to choose the current month every time they look at a report.

Tag: Time dimension

Compare Week Last Year for a 53 Week Year Using MDX