How to Calculate YTD on Calculated Measures

Hi ,

I am having to calculate the YTD and Twelve months to Date for calculated measures. For example, i have a calculated measure: Close Ratio=(X/Y) . So how i write an MDX to get YTD values for this calculated ratio.

Its not as simple as adding this calculated measure in the scope statement like the regular measures. It looks like For calculating close ratio -YTD[QTR4] = (X[qtr1] + X[qtr2]+ X[qtr3]+ X[qtr4] ) / (Y[QTR1] + Y[QTR2] + Y[QTR3] + Y[QTR4]) .

My requirement is to dynamically calculate YTD values for the calculated measures like ratios and % values. Can any one give me an idea how to go about writing an MDX for this?

If you have AS 2005 Enterprise Edition, you can use the Time Intelligence Wizard to generate calculations like YTD; otherwise, this article may help you craft them manually. The application of Aggregate() on a separate calculation hierarchy should enable YTD to work with a ratio calculated measure:

http://www.sqlmag.com/articles/index.cfm?articleid=46157&

>>

[June 2005]

Analysis Services 2005 Brings You Automated Time Intelligence

The Business Intelligence Wizard makes time analysis a snap

By: Mosha Pasumansky , Robert Zare |||

Do you create YTD member as Measure or as a member on Time dimension or on an utility dimension.

If you create an YTD not as Measure, you shouldn't have any problem.

For example

create member [Utility].[ytd] as Aggregate(PeriodsToDate([TimeDim].currentMember, [TimeDim].[YearLevel]), Measures.CurrentMember)

That is all.