ssas - MDX query different combinations of different dimensions -

i have cube defined dimensions:

• date (year, month, week, date)
• product

and measures:

• sales amount
• units

every week select group of products promoted (i.e., "on sale"). query i'm trying write is, @ given point in time, total sales , units sold of promoted items in last n weeks.

i can write query gets me these values 1 week - this:

select    non empty { [measures].[sales amount], [measures].[units] } on columns    [cube]    (       { [product].[product].[product].&[producta], [product].[product].[product].&[productb] },       [date].[week].[week].[week 8]    ) 

what unable express in mdx is, "for week 8, give me sales these products, , week 9 give me sales these other products", , on.

the concept of products being promoted not modeled in way in cube. have considered doing i'm not sure how achieve (an scd, pehaps?).

any appreciated. thanks.

you can create set of tuples - long each tuple in set has same dimensionality:

select    non empty      {       [measures].[sales amount]      ,[measures].[units]     } on 0  ,non empty      {       (         [product].[product].[product].&[producta]        ,[date].[week].[week].[week 8]       )      ,(         [product].[product].[product].&[productb]        ,[date].[week].[week].[week 9]       )     } on 1 [cube]; 

or cross join members each hierarchy if want see possible existing combinations of each:

select    non empty      {       [measures].[sales amount]      ,[measures].[units]     } on 0  ,non empty      [product].[product].members * [date].[week].members on 1 [cube];