Hi,

I am newby in MDX. I try to make a calculated measure which sum all of
month in a year period.
But I have a problem.

Example:

I use Adventure Works Cube in Analysis Service 2005.
I want to make 2 calculated Measure. Let's call Sales This Year and
Sales Last Year.

If I filter my data in July 2006.

For Measure Sales This Year :
thus my detail data should show sum all of month in 2006.

and For Measure Sales Last Year :
thus, my detail data should show sum all of month in 2005.

perhaps, I have to make different MDX for every Calculated Measure.
I don't want to use ParallelPeriod in MDX Function
because If I filter data in July 2006 thus the detail data will be only
for July, but actually I want Sum All of Month in 2006.

How to make this MDX ....?

Thanks in Advances,

HP

Is this query along the lines of what you want?

Member [Measures].[Sales This Year] as
([Measures].[Sales Amount],
Ancestor([Date].[Calendar].CurrentMember,
[Date].[Calendar].[Calendar Year])),
FORMAT_STRING = "Currency"
Member [Measures].[Sales Last Year] as
([Measures].[Sales Amount],
Ancestor([Date].[Calendar].CurrentMember,
[Date].[Calendar].[Calendar Year]).PrevMember),
FORMAT_STRING = "Currency"
select {[Measures].[Sales Amount],
[Measures].[Sales This Year],
[Measures].[Sales Last Year]} on 0,
{[Date].[Calendar].[Calendar Year].&[2003],
[Date].[Calendar].[Month].&[2003]&[7],
[Date].[Calendar].[Date].&[756]} on 1
from [Adventure Works]
--------------------------------------------------
Sales Amount Sales This Year Sales Last Year
CY 2003 $41,993,729.72 $41,993,729.72 $30,674,773.18
July 2003 $3,552,319.38 $41,993,729.72 $30,674,773.18
July 26, 2003 $37,173.29 $41,993,729.72 $30,674,773.18

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,

Thx very much. it works fine.

Regards,
HP

Deepak Puri menuliskan: