I have a cube with an [Events] dimension, which has many-to-many relationship with the [Calendar] dimension. For example, Valentines (event) corresponds to weeks 1 through 13; Spring Sales (event) corresponds to weeks 4 through 25, etc.
I need to create a calculated member for the sales on the first week of each event and use the static value in other formulas. So, for example, I need a value for week 1 for Valentines; week 4 value for Spring Sales. In addition, the [Element] dimension should be equal to [Element].[Element]..
Furthermore, I need to create a calculated member with cumulative sales for each event for each week.
I've tried various combinations, but unsuccessful so far. Any ideas would definitely help!