MDX - Getting the last 6 month Dynamically

Question

I have a Dim table that called DimAccounts. It is describing a User account and the dates that the account has been created. for Example:

I would like to get the list of accounts that have been created on the last 6 month Dynamically. For example today is the 09\01\2016. So my list of accounts will be the accounts created from the 01-08-2015 until 09-01-2016. Notice that this CreatedOn field does not have a hierarchy and it is an attribute of the accounts dimension .

score 5 · Accepted Answer · answered Jan 11 '16 at 22:23

Assuming you have properly set the ValueColumn property of your CreatedOn attribute in your dimension (so that MemberValue comes back a date data type) then the following should work:

select {[Measures].[Your Measure Here]} on 0,
[DimAccounts].[AccountPK].[AccountPK].Members on 1
from [Your Cube]
where {
 Filter(
  [DimAccounts].[CreatedOn].[CreatedOn].Members
  ,[DimAccounts].[CreatedOn].CurrentMember.MemberValue > DateAdd("m",-6,Now())
 )
}

Or if you want to create a named set so that any user can just drop it on rows, put this in the calculations tab of your cube designer:

CREATE SET CurrentCube.[Accounts Created In Last 6 Months]
as
Exists(
 [DimAccounts].[AccountPK].[AccountPK].Members,
 Filter(
  [DimAccounts].[CreatedOn].[CreatedOn].Members
  ,[DimAccounts].[CreatedOn].CurrentMember.MemberValue > DateAdd("m",-6,Now())
 )
);

MDX - Getting the last 6 month Dynamically

1 Answers1