Answers

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]))

All replies

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

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".

Does the following MDX meet your requirement? Please refer to the following Adventure Works sample:
WITH MEMBER [Date].[Calendar].[Calender2001] AS
Aggregate(
PeriodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].[Calendar Year].&[2001]
)
)

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".

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.

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.

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]))

Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.