Sql-server – MDX – Getting the last 6 month Dynamically

dimensionmdxsql serverssas

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:

enter image description here

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 .

Best Answer

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())
 )
);
Related Question