SSAS MDX for SUM (DISTINCT Customer's (MAX (Date's Weight)))

Question

To the MDX gurus, I have been beating my head against this one for a week and I am nowhere close to solving it. Can it be solved?

Here's the challenge:

To create a Calculated Member Expression in SSAS BIDs to calculate the Weighted_Members which is described as the following: "For any date period chosen, we need to calculate the sum of the Weights that is associated with the most recent visit of a unique member."

In pseudo-code: SUM(DISTINCT Member’s (MAX (Date’s Weight)))

NOTES: * The WEIGHT is given to a member’s visit to a particular location and is applicable for 1 month.

Here is a sample of the fact table showing: * Two members (membership id: 100 and 103) * Visiting 3 different locations (location id: 200, 220 and 230) * At different dates throughout 2014 and 2015.

Visits_F_ID | Visit_Date | Membership_ID | Location_ID | Weights |

     1  |  Jan 1, 2014    |        100      |  230         |  3.5      |
     2  |  Mar 1, 2014    |        100      |  220         |  2.0      |
     3  |  May 1, 2015    |        100      |  220         |  2.5      |
     4  |  Apr 1, 2014    |        103      |  200         |  1.0      |
     5  |  Jul 1, 2014    |        103      |  220         |  1.5      |
     6  |  Sep 1, 2014    |        103      |  230         |  0.5      |
     7  |  Nov 1, 2014    |        103      |  220         |  3.0      |
     8  |  Jan 1, 2015    |        103      |  220         |  1.0      |
     9  |  Aug 1, 2015    |        103      |  200         |  7.0      |
    10  |  Sep 1, 2015    |        103      |  230         |  4.5      |
    11  |  Dec 1, 2015    |        103      |  200         |  1.5      |

Dimensions:

The Visit Date Dimension has the following attributes: * YEAR * Quarter * MONTH * Date * Calendar Year->Quarter->Month->Date (calendar_quarter_hierarchy) * Calendar Year->Month->Date (calendar_month_hierarchy)

The Membership Dimension has the following attributes: * membership_id (currently visibility set to false (or hidden) as there are >5M records) * Gender * Age Cohort

The Location Dimension has the following attributes: * Location_ID * Location_Name * City * Province * Province->City->Location_Name (Geographical_hierarchy)

Examples:

Example #1.) The Weighted_Members for the YEAR 2014 would be calculated as follows: STEP 1: filtering the fact data for activity in YEAR 2014.

        Visits_F_ID |  Visit_Date     |  Membership_ID  |  Location_ID |  Weights  |
        =============================================================================
                 1  |  Jan 1, 2014    |        100      |  230         |  2.5      |
                 2  |  Mar 1, 2014    |        100      |  220         |  2.0      |
                 4  |  Apr 1, 2014    |        103      |  200         |  1.0      |
                 5  |  Jul 1, 2014    |        103      |  220         |  1.5      |
                 6  |  Sep 1, 2014    |        103      |  230         |  0.5      |
                 7  |  Nov 1, 2014    |        103      |  220         |  3.0      |

STEP 2: taking the data with the most recent date for each unique member from the above:

        Visits_F_ID |  Visit_Date     |  Membership_ID  |  Location_ID |  Weights  |
        =============================================================================
                 2  |  Mar 1, 2014    |        100      |  220         |  2.0      |
                 7  |  Nov 1, 2014    |        103      |  220         |  3.0      |

STEP 3: sum the Weights to give the Weighted_Members = 2.0 + 3.0 is 5.0

====== Example #2.) If the cube user slices for the time period of 2015, following the same three steps in example #1 above, the Weighted_Members:

        Visits_F_ID |  Visit_Date     |  Membership_ID  |  Location_ID |  Weights  |
        =============================================================================
                 3  |  May 1, 2015    |        100      |  220         |  2.5      |
                11  |  Dec 1, 2015    |        103      |  200         |  1.5      |

Weighted_Members = 2.5 + 1.5 is 4.0

====== Example #3.) If the cube user slices for the time period of Mar 2014 to Oct 2014 and is interested in visits to location_id = 220, the Weighted_Members:

        Visits_F_ID |  Visit_Date     |  Membership_ID  |  Location_ID |  Weights  |
        =============================================================================
                 2  |  Mar 1, 2014    |        100      |  220         |  2.0      |
                 5  |  Jul 1, 2014    |        103      |  220         |  1.5      |

Weighted_Members = 2.0 + 1.5 is 3.5

====== Example #4.) If the cube user slices for the time period of July 2015 to Aug 2015, the Weighted_Members:

        Visits_F_ID |  Visit_Date     |  Membership_ID  |  Location_ID |  Weights  |
        =============================================================================
                 9  |  Aug 1, 2015    |        103      |  200         |  7.0      |

Weighted_Members = 7.0

Could you clarify why only records 6, 5 and 2 are considered when all the records have a date value in year 2014? — vmachan
What are the hierarchies, dimensions and measures in your cube? — SouravA
@vmachan sorry for the delay in responding, I didn't receive a notification that people commented in my post. Still new to this platform -- to answer your first question, from my sample data, there are 3 distinct customers for the year of 2014, record_ids 6,5 and 2 corresponds to the max Date for Customer_ids 1,3 and 2 respectively. — incognito_j0e
@SouravA, we are using the Date dimension with a ([year]->[quarter]->[month]->[Date] and [year]->[month]->[Date]) hierarchies. Customer dimension with no hierarchies, the location dimension with 2 hierarchies ([state]->[city]->[store_location_id] and [geography cardinality]->[store_location_id] ). In addition to the Weighted_Customers, we also have SUM(Weight) as a measure. There are a few measure columns that are not included in the sample data that we aggregate to have as measures as well such as SPEND, TAX, QTY, etc. — incognito_j0e

SouravA SouravA · Accepted Answer · 2015-12-21T17:40:10

Based on my understanding - You can give this a try:

WITH MEMBER Measures.YourCalcMember AS
SUM 
    (
    generate
        (
            Customer.CustomerID.MEMBERS AS S,
            s.CURRENT *
            TAIL(
                NonEmpty
                    (
                     [Date].[Date].[Date].MEMBERS, --The last date for the "current" customer
                     (s.CURRENT, [Measures].[Weight])
                    )
                )
        )
     , 
    Measures.[Weight]
    )

SELECT Measures.YourCalcMember ON 0,
Location.LocationID.MEMBERS ON 1
FROM 
(
 SELECT [Date].[Year].&[2014] ON 0 FROM [Your Cube] --The year filter
)

Using the "generate" function, loop thru the customers and obtain a cross-set of customerId and the 'last' date for that customer. Over this set then, obtain the sum of weights.

All said, further details are needed before this question can be attempted correctly.