Search code examples
sql-serverssasmdx

Measure for a month and its previous using a named set SSAS

I have a named set that returns the last 10 weeks from the current week. In the cube browser I get the value of a measure for each week. I want to create another measure that contains the value of the previous week returned by the named set. Something like this :

Weeks       Measure1     Measure2
Week 1         50           40
Week 2         35           50
Week 3         77           35

How to do this using MDX ?

Solution

  • Measure2 will be a tuple of whatever measure you want to show - lets calls it [Measures].[Revenue] and the currentmember of the hierarchy used in the Weeks column lagged by 1.

    I don't know the structure of your cube so you'll need to adjust the following:

    (
  [Measures].[Revenue],
  [Date].[Calendar Week of Year].CURRENTMEMBER.LAG(1)
)