SSRS compare week to the same week number from previous year

Question

I am working on an SSRS report that gets its data from an OLAP cube. In the OLAP cube I have a field named WeekOfYear which gives me the week number of the year based on the date. For example, week 1 for January 1st (if January 1st falls on a Monday) and week 2 for January 8th. My data is grouped by this field but now I want to be able to compare the data from this week of the year to the previous year's week of the year. Like comparing Week 1 of 2015 to Week 1 of 2014. Is there anyway that I can accomplish this? I appreciate any help. Thanks.

Answer 1

There is a LookUp function that should be able to do what you need.

You mention you have a WeekOfYear field. This also assumes you have a Year field (or can calculate it with **YEAR(Fields!YouDateField.Value) )

=LookUp(Fields!YourYearField.Value - 1 & "|" & Fields!WeekOfYear.Value, Fields!YourYearField.Value & "|" & Fields!WeekOfYear.Value, Fields!YourValueField.Value, "YourDataSet")

If you are actually summing multiple rows of data from your cube, you would need to use LookUpSet to get all the values and sum them with a custom function (since Microsoft couldn't possibly envision users wanting to SUM multiple records). Luckily users have already created a function - SumLookup. See How to combine aggregates within a group with aggregates across groups within SSRS if needed.

Answer 2

If you have access to the OLAP cube and can edit this, you could define a new Calculated Measure on the cube. How exactly this would work depends on the set up of your date hierarchies. You can also access this and define calculated measures through the Query Designer while constructing your dataset in Report Builder/Visual Studio.

Right-click in the cube browser and choose "New Calculated Member".

Reporting on weeks across years can be difficult due to the fact that the number 7 doesn't fit neatly into 365 or 366, so you always end up with a little over 52 weeks. Since the 1st of January could be a Sunday one year, and a Tuesday on the next (2012/2013), it's not always a good idea to directly compare these. So people may work around this by defining the 7-day weeks for the year against their date dimension. One year you may have 52 weeks, another you'd have 53. This is a little off topic, so I'll link to an explanation of this here, but it is important to be aware of this in order to implement my suggestion below.

Assuming you have a nice hierarchy on your date dimension that can aggregate up Weeks to Year level, you can create a new measure in your cube using the ParallelPeriod function.

[Measures].[SalesSPLY] AS  
   (
        ParallelPeriod
        (
            [Dim Date].[ReportingCalendar].[ReportingYear],
            1,
            [Dim Date].[ReportingCalendar].CurrentMember
        ),
        [Measures].[Sales]
    )

My example MDX assumes that you already have a hierarchy called ReportingCalendar created on your date dimension. Yours may be named differently.

Now if you browse your cube and select WeekOfYear, Sales, and SalesSPLY, you will see your value for this year's week 1, alongside last year's.

OLAP cubes are very good at this type of time-based intelligence, as they can very quickly provide aggregated and offset data in a way that would be slower to run in an RDBMS or within SSRS itself.