Customer Sales based on YTM dimension

Hello,

We have a cube that has customer sales data for last 5 years. Time Dimension displays the YTM hierarchy.

=> Selecting "month" is a parameter for the user on a Reporting Service report. Once he selects a month from YTM hierarchy - how to get list of "only those customers" whose sales have been continuously below say 80,000 dollars, beginning the month he selects as a "start month" until "next 6 months".

Multiple selection - not allowed. Only one month can be selected by user at any time.

Any help highly appreciated.

Thanks,

RajShri

Here's an Adventure Works example, which lists all customers with < $1000 in sales for each of 6 months, starting with the selected month (here, Jan. 2004). Note that this includes customers with no sales as well:

>>

With

Member [Measures].[Max6MonthSales] as

Max(LastPeriods(-6,

OpeningPeriod([Date].[Calendar].[Month])),

[Measures].[Internet Sales Amount])

Set [LowSalesCustomers] as

Filter([Customer].[Customer Geography].[Full Name].Members,

[Measures].[Max6MonthSales] < 1000)

select {[Measures].[Internet Sales Amount],

[Measures].[Max6MonthSales]} on 0,

[LowSalesCustomers] on 1

from [Adventure Works]

where [Date].[Calendar].[Month].&[2004]&[1]

>>

sql