MDX – Getting the last 6 month Dynamically

Posted on

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:

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 0912016.
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 .

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 {
  ,[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]
  ,[DimAccounts].[CreatedOn].CurrentMember.MemberValue > DateAdd("m",-6,Now())

Leave a Reply

Your email address will not be published. Required fields are marked *