Aggregate on a Calculated Measure

I am trying to calculate aggregate on a calculate measure. See below for code. I get an error saying I cannot aggregate over calculated member in the measures dimension. I need to calculate MTD and YTD values for a calculated member. Is there any way I can do this?

Any help will be appreciated.

Thanks

Ann

Aggregate

(

PeriodsToDate

(

[Time].[Time].[Month],

[Time].[Time].CurrentMember

),

(

SUM({[Order Type].[Order Type].&[Membership Renewal Order],[Order Type].[Order Type].&[Membership Renewal with Autoship Order],[Order Type].[Order Type].&[New Membership Order],[Order Type].[Order Type].&[New Membership with Autoship Order]},[Measures].[Sale Amount]))

Try replacing the "Aggregate" function in your statement with "SUM". Since you are not attempting to aggregate a "Distinct Count" or some other semi-additive measure this should give you the result you are looking for. If you want create MTD, YTD or other period calculations that work with multiple measures your best bet is to use a "shell dimension" that will allow the creation of members using the "Aggregate" function. You can use the Business Intelligence Wizard to add time calculations, which will automate the process of creating the shell dimension for you. There are some bugs in the current version, but there is a workaround posted at the link below and SP1 includes a fix that addresses the issue.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;912136

HTH,

- Steve