Search code examples
ssasmdx

Accessing previous member based on strtomember


I've created a simple named set [Latest] which uses the previous day's date. This works as required, and can be used by any measures that reference the date dimension:

CREATE DYNAMIC SET CURRENTCUBE.[Latest]
AS strtomember('[Date].[Date Key].&['+ 
vba!Format(DateAdd('d',-1,Now()),'yyyyMMdd')+']'); 

Now I have a requirement to go back an additional business day, and to have this displayed as a separate calculation.

The date dimension is populated only with business dates, but also has an IsBusinessDay attribute. As the 'Latest' calculation uses the dateadd vba function, I can't simply increase the dateadd parameter, as there are gaps in the dates (for holidays/weekends) (though I have the option to go back to the source view and change this if required).

I was able achieve the create a working calculation when using a specific measure, but what I am looking to achieve is to have a single named set calculation that that can be used with any measure, and not hard-coded into the calculation like this:

CREATE MEMBER [TwoDaysBack] AS
( 
      [Date].[Date Key].currentmember.prevmember, 
      [Measures].[PL] 
    ) ;

Any suggestion or pointers greatly appreciated.


Solution

  • As per my understanding, you just have bussiness days in your Date dimension. So for any member in your date dimension ,Prevmember will get the day before the last date. If that is the case your initial named set will work with a slight change.

    CREATE DYNAMIC SET CURRENTCUBE.[Latest]
    AS strtomember('[Date].[Date Key].&['+ 
    vba!Format(DateAdd('d',-1,Now()),'yyyyMMdd')+']').prevmember;