MDX to get First Sales Amount

Question

I'm currently attempting to use Calculated Members to return the Sales Amount for the first sale by a customer. I'm using some code that I've slightly modified by looking at various blogs but am encountering some problems in adapting it in a way that is consistent with the result set I am looking for.

I am trying to create several Calculated Members, one to return the first order date of a particular product, which I have. I would like to take that date now and return the Sales of that product for only that day. This is where I am running into a problem currently as whenever I put a Date Axis into the equation, unless it is at the Day level of the hierarchy, the CurrentMember function will aggregate to whatever level and cause some inconsistencies.

Here is the General of what I have currently

WITH
MEMBER [Measures].[DaysToDate] AS 
COUNT(NULL:[Date].[Fiscal].CURRENTMEMBER)


MEMBER [Measures].[HadSale] AS
IIF([Measures].[Product Sales]=0,NULL,[Measures].[DaysToDate])


MEMBER [Measures].[MinDate] AS
MIN(NULL:[Date].[Fiscal].CURRENTMEMBER, [Measures].[HadSale])


MEMBER [Measures].[FirstSaleDay] AS
IIF(ISEMPTY([Measures].[MinDate]), NULL,

    [Date].[Fiscal].[Day].MEMBERS.ITEM([Measures].[MinDate]).NAME)


SELECT {
    [Measures].[DaysToDate]
    ,[Measures].[HadSale]
    ,[Measures].[MinDate]
    ,[Measures].[FirstSaleDay]
    } ON 0,
[Sales Team].[Sales Team].[Customer].MEMBERS ON 1
FROM [Sales and Marketing]

This code will work if I put a WHERE clause in at the Day level for the previous day (which is our last process time). But if I put in lets say Fiscal Quarter, or place on the on the columns, which is a level higher than Day, it then counts the CurrentMembers based off of the Quarters instead of the Days and returns incorrect days due to the pass inside of the ITEM() in [FirstSaleDay].

I'm mostly looking for a way to force these calculation to operate at the day level as opposed to whatever level of the hierarchy happens to be getting passed in to the final query.

Any help would be greatly appreciated.

Mikuana Mikuana · Accepted Answer · 2014-10-15T18:25:52

I haven't answered your entire question, but since you end this saying that you're mostly looking for a way to force the calculated member to use the "day" level of your hierarchy, I think this should do it for you. It's a technique I've used in the past.

MEMBER x As Descendants([Date].[Fiscal].CurrentMember,4,SELF).Item(0).Value

This formula first looks at your calendar, and navigates to the top of the hierarchy (that's the SELF in the formula). Then it navigates down a specified number of levels. In my calendar I have four levels, but yours may be different so you will need to adjust that if it is. This is a set, but you can get the first member using .Item(0), assuming your calendar is ordered in typical fashion. The .Value is not necessary, but helps in testing as it allows you to define this formula as a member by itself.

MDX to get First Sales Amount

1 Answers