Can it be done with mdx?

I have the following calculated member included in a dataset for a report:

WITH MEMBER MEASURES.TwoYearsSum AS
AGGREGATE({[DIM_Time].[calendar_Year].CURRENTMEMBER,
[DIM_Year].[calendar_Year].CURRENTMEMBER.PREVMEMBER},
[Measures].[customer_id Distinct Count])

What I need is to further filter out in this aggregate measure "customer_id
distinct count" only for the customer_id with the minimum sales_date based
on the first aggregate condition
"AGGREGATE({[DIM_Time].[calendar_Year].CURRENTMEMBER,
[DIM_Time].[calendar_Year].CURRENTMEMBER.PREVMEMBER}...."

The dimensions include in the olap cube are:

dimensions: [dim_time]
[dim_time].[calendar_year]
[dim_time].[sales_date]
dimensions: [dim_customer]
[dim_customer].[customer_id]
dimensions: [dim_store]
[dim_time].[store].[city]
[dim_time].[store].[store_name]

Could you explain what you want at a higher level, with data examples? It's not clear how the minimum sales_date relates to the 2 years being aggregated.|||

Hi Deepak Puri:

Thank you for your reply.

For example data in the olap cube:

customer_id.........city............calendar_year.............sales_date

1..............vancouver............2006.....................10-apr-2006.......min sales_date

1..............vancouver...........2006.....................15-dec-2006

1................calgary.............2007......................26-feb-2007

1..............new york.............2007.....................16-feb-2007

2................calgary............2006.....................13-mar-2006.......min sales_date

2..............new york............2007.....................18-may-2007

2...............calgary.............2007......................29-nov-2007

2..............new york............2007......................10-jul-2007

if the two years aggregate is 2006 and 2007, then the dataset for the distinct customer_id count should return the follows:

city..................distinct_count

vancouver..................1........................only the row with the sales_date 10-apr-2006 (min sales_date) is counted

calgary......................1.......................only the row with the sales-date 13-mar-2006 (min sales_date) is counted

new york...................0........................customer_id can only be counted once, all other records are not couted for other cities.

I hope my explaination is clear.

Thanks

|||

Translating your problem (as I understood it) to Adventure Works, here's a sample query for the count of customers for whom the earliest sale in the selected time period (2002 and 2003) occurred in a particular Product Category. To facilitate verification of the results, the query is limited to customers in Calgary. I adapted a sample from Chris Webb's blog to filter the earliest order date for each customer:

Code Snippet

WITH SET [FirstSales] AS

FILTER(NONEMPTY(

[Customer].[Customer Geography].[Customer].MEMBERS

* [Date].[Date].[Date].MEMBERS

, [Measures].[Internet Sales Amount])

AS MYSET,

MYSET.CURRENTORDINAL = 1 or

NOT(MYSET.CURRENT.ITEM(0) IS MYSET.ITEM(MYSET.CURRENTORDINAL-2).ITEM(0)))

Member [Measures].[CustomersW/FirstSales] as

Count(NonEmpty([FirstSales], [Measures].[Internet Sales Amount])),

FORMAT_STRING = '#,#'

select

{[Measures].[Internet Sales Amount],

[Measures].[CustomersW/FirstSales]} on 0,

[Product].[Product Categories].[Category] on 1

from [Adventure Works]

where ({[Date].[Calendar].[Calendar Year].&[2002],

[Date].[Calendar].[Calendar Year].&[2003]},

[Customer].[Customer Geography].[City].&[Calgary]&[AB])

Internet Sales Amount CustomersW/FirstSales
Accessories $254.23 3
Bikes $12,199.16 5
Clothing $112.97 3
Components (null) 0