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.

How to Calculate Sum for distinct values in MDX

Hi ALL,
I need help in calculating sum of market value based on property_id. The sum should be calculated by finding the average market value for a given property and then sum the individual average of the property to get the Distinct sum.

I have a very little knowlegde in Cubes and analysis services. I need to perform this distinct sum by using calculated members using MDX on SQL server 2000.

Data example

P_Code Cus Prpty_Id Mrkt Val

3000 1234 1111 $10,000 $10,000
3000 1234 2222 $20,000
3000 1234 3333 $30,000 $20,000
3000 5678 1111 $10,000
3000 5678 2222 $20,000 $30,000
3000 5678 3333 $30,000
3000 1020 1111 $10,000
3000 1020 3333 $30,000

Distinct Sum $60,000

Thanks in Advance

BrijeshWhat about SELECT SUM(DISTINCT Mrkt)
FROM tbl
GROUP BY P_Code|||Nope: http://weblogs.sqlteam.com/jeffs/archive/2007/07/31/60274.aspx