Creating a This Year Last Year Calculation for SSAS Cube
In this quick demo I’m going to add a calculated member to the AdventureWorks cube that allows an end user to do a This Year/ Last Year comparison for a given measure.
To do this tutorial you must have the AdventureWorks database AdvertureWoksDW2008 in place on your server. You will also need the AdvertureWorks 2008 SSAS project.
Step 1. Open up the Adventure Works SSAS 2008 Enterprise project. Go to the Adventure Works data source view. Right click on the Date table and add a named calculation as shown below to the Date table. Click Save.
Step 2. Add the new [Date – Calculations] column as a dimension attribute of the Date dimension and set the attribute properties. To do this, drag the [Date – Calculations] column from the Data Source View to the Attributes window. Set the properties for the Date – Calculations attribute as shown below. Click Save.
Step 3. Add the Last Year calculation to the cube as shown below.
Step 4. Add a scope to the Last Year calculated member as shown below.
Step 5. Deploy the cube. Now let’s see what our calculation looks like.
In this demo the Date- Calculation only works for the Date dimension, and Reseller Sales Amount measure. It doesn’t work for Delivery Date or Shipping Date. Delivery Date and Shipping Date would need to be scoped separately as well as additional measures.
To use the Date – Calculation an attribute from the Date dimension must be on one of the axis.
Hope this post was helpful.