CODEWITHSUNDEEP
ssasmdx
Blackmoor
Blackmoor

Reputation: 85

Accessing previous member based on strtomember and using additional dimension attribute

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 references the date dimension:

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

My aim is to calculate the delta between this value and the previous weekday, and to have this displayed as a separate calculation. [Latest] | [Latest-1] | [Latest-Delta]

I started creating an additional calculation using [Latest].prevmember:

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

This worked fine, but only if the previous day was a weekday. When the previous day is a weekend there will be no change to the value, so therefore delta will be 0. To be clear, if [Latest] returned a Tuesday, then Latest-1 would be the prevmember of weekday Monday, but if Latest returns a Monday date, I'd want Latest-1 to be use the previous weekday of Friday.

The date dimension is populated with a full calendar of dates, but has as IsWeekday boolean flag which is 0 for weekends/holidays, and 1 for weekdays.

The logic of this is very simple, but I am struggling to think out how I incorporate the IsWeekday attribute into the cube calculation.

Any pointers greatly appreciated.

Upvotes: 1

Views: 83

Answers (1)

MrHappyHead
MrHappyHead

Reputation: 442

Off the top of my head I think you need to get a SET of date keys that are weekdays IsWeekDay=1. Then you need to Filter this set where the key is less than the Latest key. Finally you can take the Head of that Filtered set.

Something like this (need to double check syntax) :

StrToMember( TAIL ( FILTER(Date.IsWeekDay.&[1].members, date.IsWeekday.currentmember.memberkey< yyyymmdd,

1) )

where the yyyymmdd is the expression for the current date.

Hth,

MrHH

Upvotes: 1

Related Questions