- Go to your dsv of the cube and add a new Named Calculation field to your time dimension, Make sure to call it PeriodID and the expression should be " N'Current Period' " (make sure to drop the double quotes
- Create a new Dimension with the Dimension Wizard
- Select use an existing table
- tap into your current time dimension, in my case I am using DimWeek
- Make sure to have the primary key of the dimension selected and no other fields
- Name the new dimension "Time Calculation"
- Now that the dimension is created bring it up
- Drag over the PeriodID (the Name Calculation you created on step 1) to the Attributes section of the Dimension Structure tab
- Rename the field to Time Calculation and you should have something like this
- Now open the cube up and go to the Calculation
- Create a new Calculated Member
- Name: [Prior Year]
- Parent hierarchy: Time Calculation. Time Calculation
- Expression: (parallelperiod([Week].[FiscalCalendar].[Fiscal Year], 1, [Week].[FiscalCalendar].CurrentMember), [Time Calculation].[Time Calculation].&[Current Period])
- Now you need to setup your Dimension Usage
- Setup the Measure Group, Referenced, Intermediate Dimension (Week in this case), Reference Dimension attribute (Time Calculation), Intermediate Dimension attribute (PeriodID)
- Build and deploy cube
- Now in your cube browser you will see a new dimension call Time Calculation
- Add the new dimension and drag a metric and the time dimension and you will see the power of the Time Calculation Dimension

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Prior Year]

AS (parallelperiod([Week].[FiscalCalendar].[Fiscal Year], 1, [Week].[FiscalCalendar].CurrentMember), [Time Calculation].[Time Calculation].&[Current Period]),

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Year to Date]

AS aggregate(crossjoin([Time Calculation].[Time Calculation].&[Current Period], PeriodsToDate([Week].[FiscalCalendar].[Fiscal Year], [Week].[FiscalCalendar].CurrentMember))),

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Year to Year Change]

AS [Time Calculation].[Current Period] - [Time Calculation].[Prior Year],

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Year to Year Change %]

AS Case

When ISEMPTY([Time Calculation].[Prior Year]) Or [Time Calculation].[Prior Year] = 0

Then IIF([Time Calculation].[Current Period] > 0, 1, 0)

Else

[Time Calculation].[Year to Year Change] / [Time Calculation].[Prior Year]

End,

FORMAT_STRING = "Percent",

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Prior 3 Periods]

AS Aggregate({[Week].[FiscalCalendar].CurrentMember.Lag(3) :

[Week].[FiscalCalendar].CurrentMember.Lag(1)}, [Time Calculation].[Current Period]),

VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Prior 6 Periods]

AS Aggregate({[Week].[FiscalCalendar].CurrentMember.Lag(7) :

[Week].[FiscalCalendar].CurrentMember.Lag(1)}, [Time Calculation].[Current Period]),

VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Year to Date %]

AS [Time Calculation].[Time Calculation].&[Current Period] /[Time Calculation].[Time Calculation].[Year to Date],

FORMAT_STRING = "Percent",

VISIBLE = 1;

## 1 comments:

I have created an exact same Dimension, but I encounter an issue when I want to use the Calculated Members of the dimension in a report.

I get the error: A set has been encountered that cannot contain calculated members.

Could you help me out? (http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/9e06b045-be2f-42e5-ac0f-69d14f001501)

## Post a Comment