3

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 .

5

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())
 )
);
  • thanks for your answer! , My solution was to add a flag column to the table that will show 1 if an account created in past 6 month or not. your solution Helps me a lot also :) – Shmuel Milavski Jan 21 '16 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.