# Using two levels from the same hierarchy on different axis for YTD

### Question

• I have a Fiscal date hierarchy with two levels: Fiscal year and Fiscal month. I use this hierarchy in a year-to-date calculation using the PeriodsToDate function.

I want to see the evolution of this measure for each fiscal month year after year.

2010              2011                2012
YTD                YTD                 YTD
P1         100    100      125     125       240     240

P2         125    225      110     235       150     390

P3          90    315      140     375       120     510

...

In this context, is there a way to have two levels from the same dimension on two different axis? If not, what's the best way around this limitation?

Thank you!

Simon Blais.

Tuesday, February 19, 2013 6:33 PM

• Hi Simon  ,

I hope this would work  as  you expect ,

with
member [Reseller_2005YTD]
as
sum((PERIODSTODATE([Date].[Month of Year].levels(0),[Date].[Month of Year].currentmember)*
[Date].[Calendar Year].&[2005])
,([Measures].[Reseller Sales Amount]))
member [Reseller_2005]
as ([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2005])

member [Reseller_2006YTD]
as
sum((PERIODSTODATE([Date].[Month of Year].levels(0),[Date].[Month of Year].currentmember)*
[Date].[Calendar Year].&[2006])
,([Measures].[Reseller Sales Amount]))

member [Reseller_2006]
as ([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2006])

member [Reseller_2007YTD]
as
sum((PERIODSTODATE([Date].[Month of Year].levels(0),[Date].[Month of Year].currentmember)*
[Date].[Calendar Year].&[2007])
,([Measures].[Reseller Sales Amount]))

member [Reseller_2007]
as ([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2007])

select [Date].[Month of Year].[Month of Year].members on 1 ,
{[Reseller_2005], [Reseller_2005YTD],
[Reseller_2006], [Reseller_2006YTD],
[Reseller_2007], [Reseller_2007YTD]} on 0

----------------------------------------------------------------------------------------------------------------------

• Marked as answer by Friday, March 01, 2013 8:27 PM
Friday, March 01, 2013 8:01 PM

### All replies

• Hi Simon  ,

You cannot use the same hierarchy on two different axis . We can use one level from hierarchy on one axis and we need to use the other attribute not from the same hierarchy , we need to get this attribute from other user defined hierarchies or from the attribute hierarchies. See the example below

select [Date].[Calendar Year].children  on  0 ,
[Date].[Calendar].[Month]  on 1
where
([Measures].[Internet Sales Amount])

-----------------------------------------------------------------------------------------------------------------------

Tuesday, February 19, 2013 9:09 PM
• Thank you for taking the time to answer.

Your proposition works for "non-calculations" measures but when used with a PeriodsToDate calculation, it doesn't add the values from the previous periods. I just get the current value.

Wednesday, February 20, 2013 7:17 PM
• Hi Simon,

I think you can make use of role playing dimension for this case . Keep the monthName from Date Dimension on row axis and YearName from RolePlaying dimension of same hiearchy on columns to display the PeriodsToDate measure value. But the roleplaying dimension and DateDim should be joined to fact table on the same granular level may be on "Datekey".

----------------------------------------------------------------------------------------------------------------------

Wednesday, February 20, 2013 8:14 PM
• Hi Simon,

WITH MEMBER [Date].[Calendar].[Calender2001] AS
Aggregate(
PeriodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].[Calendar Year].&[2001]
)
)

MEMBER [Date].[Calendar].[Calender2002] AS
Aggregate(
PeriodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].[Calendar Year].&[2002]
)
)

MEMBER [Date].[Calendar].[Calender2003] AS
Aggregate(
PeriodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].[Calendar Year].&[2003]
)
)

SELECT
{[Date].[Calendar].[Calender2001],[Date].[Calendar].[Calender2002],[Date].[Calendar].[Calender2003]} ON Rows,
[Date].[Calendar Year].Children ON COLUMNS
FROM
WHERE
[Measures].[Order Quantity]

Regards,

Elvis Long
TechNet Community Support

Friday, February 22, 2013 5:50 AM
• Hi Simon,

I think you can make use of role playing dimension for this case . Keep the monthName from Date Dimension on row axis and YearName from RolePlaying dimension of same hiearchy on columns to display the PeriodsToDate measure value. But the roleplaying dimension and DateDim should be joined to fact table on the same granular level may be on "Datekey".

----------------------------------------------------------------------------------------------------------------------

There's something I don't get because I can't make it work.

Here's how I defined my calculation in the cube:

CREATE MEMBER CURRENTCUBE.[Measures].[Transactions amount - YTD]
AS Aggregate(
PeriodsToDate(
[Time].[Fiscal date].[Fiscal year]
, [Time].[Fiscal date]
)
, [Measures].[Transactions amount]
),
VISIBLE = 1;

My hierarchy as two levels: Fiscal year and Fiscal month. The key for Fiscal month is Fiscal year and Fiscal month. If I query on a time attribute outside the hierarchy, like Fiscal month (without the relationship to the Fiscal Year), I just have the monthly value, not the cumulative value.

Tuesday, February 26, 2013 7:18 PM
• Hi Simon,

<snip>

Regards,

Elvis Long
TechNet Community Support

Hi Elvis,

The query you provided gives me yearly totals. What I look for would be more like :

SELECT
[Date].[Calendar Quarter of Year].Children ON Rows,
{[Date].[Calendar].[Calender2001],[Date].[Calendar].[Calender2002],[Date].[Calendar].[Calender2003]} ON COLUMNS
FROM
WHERE
[Measures].[Order Quantity]

But still, I don't get the expected results.

Tuesday, February 26, 2013 7:28 PM
• Hi Simon,

Please find the below query  which i used to calculate the  Evolution of month year after year,

```WITH
MEMBER [Measures].[Reseller Sales YTD_2005] AS
Sum( PERIODSTODATE( [Date].[Calendar].[Calendar Year] ),
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2005]) )
MEMBER [Measures].[Reseller Sales 2005] AS
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2005])

MEMBER [Measures].[Reseller Sales YTD_2006] AS
Sum( PERIODSTODATE( [Date].[Calendar].[Calendar Year] ),
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2006]) )
MEMBER [Measures].[Reseller Sales 2006] AS
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2006])

MEMBER [Measures].[Reseller Sales YTD_2007] AS
Sum( PERIODSTODATE( [Date].[Calendar].[Calendar Year] ),
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2007]) )
MEMBER [Measures].[Reseller Sales 2007] AS
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2007])
SELECT
{[Measures].[Reseller Sales 2005],[Measures].[Reseller Sales YTD_2005],
[Measures].[Reseller Sales 2006],[Measures].[Reseller Sales YTD_2006],
[Measures].[Reseller Sales 2007],[Measures].[Reseller Sales YTD_2007]} ON 0,
{[Date].[Calendar].[Month].MEMBERS } ON 1
FROM
```

----------------------------------------------------------------------------------------------------------------------

Tuesday, February 26, 2013 8:55 PM
• Hi Nedunuri,

It's not very far from what I want to achieve, but the results come out as stairs, which makes it hard for year-for-year comparison. If the results for July, August, etc. of each year could be on the same row, that would be perfect.

The only solution I see at this point would be to add the measure directly in the fact table.

Wednesday, February 27, 2013 7:25 PM
• Can you try this?

WITH
MEMBER [Measures].[Reseller Sales YTD] AS
Sum(
PeriodsToDate
(
[Date].[Calendar].[Calendar Year]
),
(
[Measures].[Reseller Sales Amount],
[Date].[Calendar Year].CurrentMember
)

SELECT
{
[Measures].[Reseller Sales Amount],

[Measures].[Reseller Sales YTD]
} ON 0,
{[Date].[Calendar].[Month].MEMBERS } ON 1
FROM

SQL Server BI Consultant (http://EasyBI.wordpress.com)

Wednesday, February 27, 2013 10:58 PM
• Hi!

I wish it was that simple. What I want is a crosstab with that information. I manually edited a picture to show what it could look like (sorry for the French labels).

Thank you!

Thursday, February 28, 2013 2:23 PM
• Do you have the option to add another attribute hierarchy - Month Of Year - similar to AdventureWorks and try this?

WITH
MEMBER [Measures].[Reseller Sales YTD] AS
Sum(
PeriodsToDate
(
[Date].[Calendar].[Calendar Year]
),
(
[Measures].[Reseller Sales Amount],
[Date].[Calendar Year].CurrentMember
)

SELECT
[Date].[Calendar Year].[Calendar Year]*
{
[Measures].[Reseller Sales Amount],
[Measures].[Reseller Sales YTD]
} ON 0,
{[Date].[Month of Year].[Month of Year].MEMBERS } ON 1
FROM

SQL Server BI Consultant (http://EasyBI.wordpress.com)

Thursday, February 28, 2013 8:10 PM
• Unfortunately, this just repeats the value instead of adding the values from the previous months.

Thanks anyway. :-)

Friday, March 01, 2013 4:09 PM
• Hi Simon  ,

I hope this would work  as  you expect ,

with
member [Reseller_2005YTD]
as
sum((PERIODSTODATE([Date].[Month of Year].levels(0),[Date].[Month of Year].currentmember)*
[Date].[Calendar Year].&[2005])
,([Measures].[Reseller Sales Amount]))
member [Reseller_2005]
as ([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2005])

member [Reseller_2006YTD]
as
sum((PERIODSTODATE([Date].[Month of Year].levels(0),[Date].[Month of Year].currentmember)*
[Date].[Calendar Year].&[2006])
,([Measures].[Reseller Sales Amount]))

member [Reseller_2006]
as ([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2006])

member [Reseller_2007YTD]
as
sum((PERIODSTODATE([Date].[Month of Year].levels(0),[Date].[Month of Year].currentmember)*
[Date].[Calendar Year].&[2007])
,([Measures].[Reseller Sales Amount]))

member [Reseller_2007]
as ([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2007])

select [Date].[Month of Year].[Month of Year].members on 1 ,
{[Reseller_2005], [Reseller_2005YTD],
[Reseller_2006], [Reseller_2006YTD],
[Reseller_2007], [Reseller_2007YTD]} on 0

----------------------------------------------------------------------------------------------------------------------

• Marked as answer by Friday, March 01, 2013 8:27 PM
Friday, March 01, 2013 8:01 PM
• Wow! That's very promising! I'll be out of the office for the next week but when I come back, I'll have to check how to adapt this to our cube.

Thanks you very much  and thanks to everyone for your help!

Simon Blais.

Friday, March 01, 2013 8:27 PM
• Sorry ! Pls try this.

WITH MEMBER YearName AS
RIGHT([Date].[Calendar Year].CurrentMember.Name,4)
MEMBER MonthName AS
[Date].[Month of Year].CurrentMember.Name
MEMBER [Measures].[Reseller Sales YTD] AS
Sum(
PeriodsToDate
(
[Date].[Calendar].[Calendar Year]
,
StrToMember("[Date].[Calendar].[" + MonthName  + " " + YearName +  "]")
)
,
[Measures].[Reseller Sales Amount]
)
SELECT
[Date].[Calendar Year].[Calendar Year]*
{
[Measures].[Reseller Sales Amount],
[Measures].[Reseller Sales YTD]
} ON 0,
{[Date].[Month of Year].[Month of Year].MEMBERS } ON 1
FROM