SSAS: dynamic filtering of date dimension member

Question

I have database and SSAS with me.

In DB:

1) fact table factSales:

+-----------------+----------+
| ColumnName      | Datatype |
+-----------------+----------+
|    JoinDate     |   date   |
| TransactionDate |   date   |
|       Amt       |   money  |
|   CustomerId    |    int   |
|    .......                 |
|    .......                 |
+-----------------+----------+

2) time dimension table dimDate

+-----------------+----------+
|   ColumnName    | Datatype |
+-----------------+----------+
|      Date       |   date   |
|   MonthNumber   |   date   |
|       Year      |    int   |
|       Week      |    int   |
|    .......                 |
|    .......                 |
+-----------------+----------+

In SSAS:

Measure group factSales (with measures: TotalAmt, Sales Count, Customer Distinct Count)
dimension JoinDate (based on dimDate)
dimension TransactionDate (based on dimDate)

I need to add a functionality to achieve:

For example, a user chooses TransactionDate from 2015-01-01 to 2015-02-01.
I need to add:

A new calculate member BaseCustomersAmt, which shows only sales made within [2015-01-01 -- 2015-02-01] period by customers where JoinDate is less than [2015-01-01] i.e. 1 year
A new calculate member NewCustomersAmt, which shows only sales made within [2015-01-01 -- 2015-02-01] period by customers where JoinDate is greater than or equal to [2015-01-01] i.e. 1 year

So, the idea is that I need to split the total sales (TotalAmt) by two groups -

first group is those who joined more than 1 year ago from chosen period,
second group the rest - who joined on or after a date which is 1 year ago from a chosen period.

Of course, the user can create two separate reports and use different ranges of Join Date, but the requirement is to do it in one report and do it automatically.

Is it possible to achieve? Please help.

mxix mxix · Accepted Answer · 2016-08-24T14:35:45

When you say

For example, user chooses Transaction Date from 2015-01-01 to 2015-02-01.

I'm going to assume it's at the Date Level and will calculate for Each date. Since i don't know if you have a Time Hierarchy with year-date relationship i'll just use LAG(365) to go 1 year back. Otherwise you can use PARALLELPERIOD

CREATE MEMBER [Measures].[BaseCustomersAmt]
AS
AGGREGATE(
    {
       NULL
       :LINKMEMBER([TransactionDate].[Date].CURRENTMEMBER,[JoinDate].[DATE]).LAG(365)
    }
    ,[Measures].[Sales]
),VISIBLE=1;


CREATE MEMBER [Measures].[NewCustomersAmt]
AS
AGGREGATE(
    {
        LINKMEMBER([TransactionDate].[Date].CURRENTMEMBER,[JoinDate].[DATE]).LAG(365)
       :LINKMEMBER([TransactionDate].[Date].CURRENTMEMBER,[JoinDate].[DATE])
    }
    ,[Measures].[Sales]
),VISIBLE=1;

EDIT:

Let's assume you have a Year-Month-Date Hierarchy.

CREATE MEMBER [Measures].[NewCustomersAmt]
AS
AGGREGATE(
    {
        ParallelPeriod(
            [JoinDate].[YearMonthDate].[Year]
            ,1
            ,LINKMEMBER([TransactionDate].[YearMonthDate].CURRENTMEMBER,[JoinDate].[YearMonthDate])
        )
       :LINKMEMBER([TransactionDate].[YearMonthDate].CURRENTMEMBER,[JoinDate].[YearMonthDate])
    }
    ,[Measures].[Sales]
),VISIBLE=1;

For a Dynamic Lag... i guess it gets more tricky... But you can try to LAG on your baseDayDimension. Don't know if will work.

.LAG(
    iif([baseDayDimension].[Day].CURRENTMEMBER IS[baseDayDimension].[Day].[All]
       ,365
       ,[baseDayDimension].[Day].CURRENTMEMBER.MEMBER_VALUE
     )
)

SSAS: dynamic filtering of date dimension member

In DB:

In SSAS:

1 Answers