How to make Calculated Measure in This Year or Last Year with MDX

How to make Calculated Measure in This Year or Last Year with MDX

Post by heru » Thu, 27 Jul 2006 14:20:39


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
 
 
 

How to make Calculated Measure in This Year or Last Year with MDX

Post by Deepak Pur » Fri, 28 Jul 2006 03:24:42

Is this query along the lines of what you want?

With
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.yqcomputer.com/ ***

 
 
 

How to make Calculated Measure in This Year or Last Year with MDX

Post by heru » Fri, 28 Jul 2006 13:24:12

Hi Deepak,

Thx very much. it works fine.

Regards,
HP

Deepak Puri menuliskan: