How can I show a last year value through a dimension in MDX?

Question

We have a relative date dimension in our cube that has member values This Year and Last Year as an example.

The user uses this set on columns against sales so they can look at Sales for this year and the same period last year.

The problem comes when they are using the Calendar Date filter to only select values for this month. If the user selects just this month, then the Last Year member disappears.

Is there a way (perhaps with scope statements) that I can tell SSAS: If the user is using these attributes and they select a specific month (or other level), then use ParallelPeriod to implicitly include the same members for the previous year so that they can see the last year sales?

If not, without using calculated members (I have so many measures that I don't want to have to duplicate them), is there a way using dimensions to show a last year value even if the user selects this year in the date dimension?

Can you include some MDX, maybe current output versus expected? Otherwise the answer is. Yes you can probably do it with a SCOPE. — mxix

mxix mxix · Accepted Answer · 2016-09-16T13:18:35

There are a few options here...

I would just add a new Calculated Member to an existing dimension,i'll add it to a Pseudo-Dimension [Time Period] dimension with something like this: (i'm pretty sure you need to add it to an existing Hierarchy. I'll assume [Relative Time])

CREATE MEMBER [Time Period].[Relative Time].[Last Year]
AS NULL
, VISIBLE=1;
SCOPE(
    DESCENDTS([Time].[YearMonthDate].[Year].MEMBERS,,AFTER)
    ,[Time Period].[Relative Time].[Last Year]
    );
    THIS = AGGREGATE(
                PARALLELPERIOD(
                    [Time].[YearMonthDate].[Year]
                    ,1
                    ,[Time].[YearMonthDate].CURRENTMEMBER
                )
                ,[Measures].CURRENTMEMBER
            );
END SCOPE;

How can I show a last year value through a dimension in MDX?

1 Answers