# Multiple Time Hierarchy

• ### Question

• Hi,

I have a calculated member that would need to be applied to 2 distinct hierarchy - ie Fiscal and Calendar

Below is a sample of how the calculation looks like. How can i create another calculation to show in Fiscal Quarter without the affecting the figures in Calendar Quarter?

```CREATE MEMBER CurrentCube.[Measures].[Days in Period] AS
Count(
Descendants(
[Date].[Calendar].CurrentMember,
[Date].[Calendar].[Date]
)
),
VISIBLE = False;

SCOPE(
[Date].[Calendar].[Calendar Quarter]
);
this = SUM(
{ [Date].[Calendar].CurrentMember.Children} ,
[Measures].[Internet Sales Amount] * [Measures].[Days in Period]
) / [Measures].[Days in Period];
END SCOPE;```
Monday, May 21, 2012 7:53 AM

• One thing you should do is make the measure [Days in Period] generic so it is not tied to the Calendar Date hierarchy.

Define it as

CREATE MEMBER CurrentCube.[Measures].[DaysinPeriod]AS Count(EXISTING [Date].[Date].[Date]),VISIBLE =False;

Then you can SCOPE it on the quarter level to look at months (since months are pointing to the same attribute in the dimension)

SCOPE([Date].[Calendar].[CalendarQuarter], [Date].[Fiscal].[FiscalQuarter]);([Measures].[Internet Sales Amount])=SUM(EXISTING [Date].[Month].[Month],[Measures].[InternetSalesAmount]*[Measures].[DaysinPeriod])/[Measures].[DaysinPeriod];ENDSCOPE;

hth

-Remember to mark as helpful/the answer if you agree with the post.

• Proposed as answer by Wednesday, May 23, 2012 3:02 AM
• Marked as answer by Monday, June 25, 2012 2:49 AM
Monday, May 21, 2012 4:01 PM

### All replies

• It is good to re-use existing members, but not in this case. The project will be more manageable with distinct members.

Cheers, Raunak | t: @raunakjhawar | My Blog

Monday, May 21, 2012 7:57 AM
• Are you trying to calculate average sales/day ? at fiscal and also at calendar hierarchy ? If so, why in SUM function you are multiplying with number of days and dividing by the same ?

# Rakesh M J | MCTS,MCITP ( SQL SERVER 2008 )

Dont forget to mark it as Answered if found useful | myspeakonbi

Monday, May 21, 2012 1:47 PM
• Hi Rakesh,

The formula above performs the computation for a business calculation as below

( [month 1 value * no. of days in month1] + [month 2 value * no. of days in month2] + [month 3 value * no. of days in month3] ) / (no. of days in that quarter)

Monday, May 21, 2012 3:39 PM
• One thing you should do is make the measure [Days in Period] generic so it is not tied to the Calendar Date hierarchy.

Define it as

CREATE MEMBER CurrentCube.[Measures].[DaysinPeriod]AS Count(EXISTING [Date].[Date].[Date]),VISIBLE =False;

Then you can SCOPE it on the quarter level to look at months (since months are pointing to the same attribute in the dimension)

SCOPE([Date].[Calendar].[CalendarQuarter], [Date].[Fiscal].[FiscalQuarter]);([Measures].[Internet Sales Amount])=SUM(EXISTING [Date].[Month].[Month],[Measures].[InternetSalesAmount]*[Measures].[DaysinPeriod])/[Measures].[DaysinPeriod];ENDSCOPE;

hth

-Remember to mark as helpful/the answer if you agree with the post.

• Proposed as answer by Wednesday, May 23, 2012 3:02 AM
• Marked as answer by Monday, June 25, 2012 2:49 AM
Monday, May 21, 2012 4:01 PM