Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Counting New and Returning Customers in MDX

with 100 comments

Finding the number of distinct customers you had in any given time period, and then finding how many of those have bought from us before and how many are new customers, is a pretty common requirement. It’s a problem I blogged about a few months ago and showed how to solve in it DAX; I was thinking about it again recently and have just updated that post with a more elegant solution. However I thought it would also be useful to show how to do these calculations in SSAS and MDX.

Here’s a query on the Adventure Works cube that shows how to calculate these values:

WITH
MEMBER MEASURES.[Returning Customers] AS
COUNT(
NONEMPTY(
NONEMPTY(
[Customer].[Customer].[Customer].MEMBERS
, [Measures].[Internet Sales Amount])
, {[Measures].[Internet Sales Amount]}
* {NULL : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER}
)
)
MEMBER MEASURES.[New Customers] AS
[Measures].[Customer Count] – MEASURES.[Returning Customers]
SELECT
{[Measures].[Customer Count]
, MEASURES.[Returning Customers]
, MEASURES.[New Customers]} ON 0,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]

There’s already a measure in the cube, [Measures].[Customer Count], that gives the distinct count of customers, so that bit’s easy. Finding the number of returning customers, ie customers who’ve bought something in this time period and who have also bought something from us before, is the tricky bit. It’s a four stage process:

  1. First, get the set of all customers:
    [Customer].[Customer].[Customer].MEMBERS
  2. Then, filter that set to get the set of all customers who bought something in the current time period. Using the NonEmpty function (as opposed to the Filter function) is the most efficient way of doing this – customers who bought something in the current time period are those who have a value for the measure Internet Sales Amount:        NONEMPTY(
    [Customer].[Customer].[Customer].MEMBERS
    , [Measures].[Internet Sales Amount])
  3. Then, take this set and find the customers in that set that bought something in all time periods previous to the current time period. Again, we can use the NonEmpty function to do this, but this time in the second parameter we want to find the customers who have a value for Internet Sales Amount for the set of time periods {NULL : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER}; for more information on how NULL and the colon operator is used here, see this post. This gives us the set expression:    NONEMPTY(
    NONEMPTY(
    [Customer].[Customer].[Customer].MEMBERS
    , [Measures].[Internet Sales Amount])
    , {[Measures].[Internet Sales Amount]}
    * {NULL : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER}
    )
  4. Finally, we need to count the number of items in this set using the Count function.

Of course, with the count of distinct customers and the count of returning customers, we can subtract the latter from the former and then get the number of new customers, ie those who’ve never bought anything from us before.

Here are the results from the Adventure Works cube:

image

 

UPDATE: if you’re hitting performance problems with this type of calculation, you might also want to read the following post http://cwebbbi.wordpress.com/2013/06/28/optimising-returning-customers-calculations-in-mdx/

Written by Chris Webb

October 8, 2010 at 1:02 pm

Posted in MDX

Tagged with ,

100 Responses

Subscribe to comments with RSS.

  1. very cool Chris! and thanks for the post on the COLON and NULL operators!

    Frank Kearney

    October 8, 2010 at 7:48 pm

  2. Hi Chris,

    Another approach to this problem is to use aggregates of the distinct count measure: [Customer Count]. The Customer dimension then need not be included in the calculation:

    WITH
    MEMBER MEASURES.[Cumulative Customers] AS
    Aggregate({Null:[Date].[Calendar].CURRENTMEMBER}
    , [Measures].[Customer Count])
    MEMBER MEASURES.[Prior Customers] AS
    Aggregate({Null:[Date].[Calendar].CURRENTMEMBER.PREVMEMBER}
    , [Measures].[Customer Count])
    MEMBER MEASURES.[Returning Customers] AS
    [Measures].[Customer Count] + MEASURES.[Prior Customers]
    – MEASURES.[Cumulative Customers]
    MEMBER MEASURES.[New Customers] AS
    [Measures].[Customer Count]
    – MEASURES.[Returning Customers]
    SELECT
    {[Measures].[Customer Count]
    , MEASURES.[Returning Customers]
    , MEASURES.[New Customers]} ON 0,
    [Date].[Calendar].[Calendar Year].MEMBERS ON 1
    FROM [Adventure Works]

    Deepak

    October 9, 2010 at 6:11 am

  3. What’s faster:

    {NULL:CurrentMember}

    or

    PeriodsToDate([All], CurrentMember)?

    QQ

    October 11, 2010 at 9:38 am

  4. Deepak – good thinking. I wonder which one is faster? I’ll have to do some tests.

    QQ – it depends. I suspect that PeriodsToDate may be faster in 2005, but with 2008/R2 there may be no difference.

    Chris Webb

    October 11, 2010 at 4:17 pm

  5. Hi,

    I’m quite new to MDX. And I wonder if it is possible, to create a named set instead of the measure with the count function.

    If I had a named set, I could use all the existing measures (sales amount, customer count, …). So I don’t have to define a member for every measure I want to see.

    So I tried to delete just the count() part. But that doesn’t seem to work …

    Thx for any help on this

    Reto E.

    January 31, 2011 at 12:19 pm

    • No, unfortunately not – a named set is just a list of things, and you can’t return a set from a measure. You need to use a function like count() to derive a value from the set, which you can then return from a measure.

      Chris Webb

      January 31, 2011 at 8:56 pm

  6. Hi again,

    What about LOST CUSTOMERS on a previous period basis.
    Again I tried, but once again I failed … ;)

    The easy part: previous period count

    WITH MEMBER MEASURES.[Returning Customers pp count] AS COUNT(     NONEMPTY(         NONEMPTY(             [Customer].[Customer].[Customer].MEMBERS         , [Measures].[Internet Sales Amount])     , {[Measures].[Internet Sales Amount]}     * {[Date].[Calendar].CURRENTMEMBER.PREVMEMBER} –only compared to pp     )
    ) MEMBER MEASURES.[Lost Customers pp count] AS
    ([Measures].[Customer Count],[Date].[Calendar].CURRENTMEMBER.PREVMEMBER) – (MEASURES.[Returning Customers pp count])

    The tricky part: previous period sales

    MEMBER MEASURES.[Returning Customers pp sales] AS SUM(     NONEMPTY(         NONEMPTY(             [Customer].[Customer].[Customer].MEMBERS         , [Measures].[Internet Sales Amount])     , {[Measures].[Internet Sales Amount]}     * {[Date].[Calendar].CURRENTMEMBER.PREVMEMBER}     )
    ,[Measures].[Internet Sales Amount]
    ) MEMBER MEASURES.[Lost Customers pp sales pp] AS
    ???
    -> set of previous period customers without set of current period customers
    -> the sum of previous period sales from this new set of customers
    ???

    Is this logic correct? And how do I get this set of customers, who only bought something in pp and not (yet) in cp?

    Thx a lot.

    Reto E.

    February 4, 2011 at 10:07 am

    • Hi Reto,

      To get the ‘lost’ customers you need to find the set of customers who bought something previously, then remove the set of customers who have bought something in this time period. You can use the Except() function for this, like so:

      WITH
      MEMBER MEASURES.[Lost Customers] AS
      COUNT(
      EXCEPT(
      NONEMPTY(
      [Customer].[Customer].[Customer].MEMBERS
      , {[Measures].[Internet Sales Amount]}
      * {NULL : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER}
      )
      ,
      NONEMPTY(
      [Customer].[Customer].[Customer].MEMBERS
      , [Measures].[Internet Sales Amount])
      )
      )

      SELECT
      {[Measures].[Customer Count]
      , MEASURES.[Lost Customers]} ON 0,
      [Date].[Calendar].[Calendar Year].MEMBERS ON 1
      FROM [Adventure Works]

      Chris Webb

      February 4, 2011 at 10:40 am

      • Thx. The except function was ‘my’ missing part.
        Sometimes it’s that easy …

        Reto E.

        February 4, 2011 at 12:48 pm

      • What I need is to find all products that were bought e.g. in the last 20 month but not in the last 3 from my current selected time period (I will use Excel as front end so I need the code for calculations in cube and not in the MMC)

        I tried things like replacing

        {NULL : [DIM Time].[Calendar].CURRENTMEMBER.PREVMEMBER}

        with

        {

        lastperiods(20,[DIM Time].[Calendar].CURRENTMEMBER) : lastperiods(3,[DIM Time].[Calendar].CURRENTMEMBER)}

        But it always ends up in an value error

        Any ideas?

        Peter

        May 25, 2011 at 3:45 pm

      • You’re getting an error because Lastperiods returns a set, and the range operator needs two members. Try something like this instead:
        [DIM Time].[Calendar].CURRENTMEMBER.LAG(20): [DIM Time].[Calendar].CURRENTMEMBER.LAG(4)

        HTH,

        Chris

        Chris Webb

        May 25, 2011 at 4:21 pm

      • hello, how do I see which lost customers, taking the other to see the gains?

        theakabroken

        November 15, 2012 at 7:45 pm

      • Can you explain what you want in a bit more detail please?

        Chris Webb

        November 15, 2012 at 9:02 pm

      • Hello

        In my project BI in SQL Server (SSAS) I need to find the lost customers and gains in time or even seller over time. My hierarchy is [Time]. [Calendar] … where Calander -> year, semester, quarter, month and day.

        So I did as you indicate here for customer gains. I think in my case fits perfectly. However, for customers not lost works well.

        Could you help me?

        -> Gain Customers:
        (
        [Measures]. [Customer Count]

        (
        COUNT (
             nonempty (
                       nonempty (
                          [Entities]. [Entity Number]. [Entity Number]. MEMBERS,
                          [Measures]. [Sale price])
                        {[Measures]. [Sale price]} *
                     {NULL: [Time]. [Calendar]. CURRENTMEMBER.PREVMEMBER}
                                             )
                                  )
        )
        )

        If crossing the seller will give the seller gains by customers, right?

        Now how do I get the number of lost customers?

        These results should return the number of customers, but how do I know specifically which customers, the NAME?

        Sorry my english.

        theakabroken

        November 15, 2012 at 10:13 pm

      • To find out how to calculated lost customers, look at the comment above in reply to Reto E.

        Chris Webb

        November 15, 2012 at 10:23 pm

    • Only works for the year.

      For the customer gains, the MDX query, checks on the period or the period imediantamente counterpart before.
      example:
      Year 2012 -> Year 2011 or January 2012 -> January 2012
      or
      January 2012 -> February 2012

      How do I do?

      theakabroken

      November 15, 2012 at 10:51 pm

      • I’m sorry, I don’t understand what you mean. Can you give an example?

        Chris Webb

        November 15, 2012 at 11:14 pm

    • Making their way to get lost customers, so it works for the year, the rest of the hierarchy gives very high values​​. For example, in 2012-33, down in the hierarchy, the 1st half of the 65th – 2nd half of 77 … and so on.

      How to get to work … can you explain the operation?

      and how to get customers which, unlike the number of customers lost / won?

      theakabroken

      November 15, 2012 at 11:20 pm

  7. Thanks Chris, this works great!

    Now I’m only searching for 2 more measures linked to this. I want to see the number of Winners and the number of Losers for a given period. So effectively the number of returning customers would be split in the number of customers who have bought more than in the previous period and the number of customers who have bought less (not nothing) than they did in the previous period.

    Rgds, Martijn

    Sandbergen

    July 28, 2011 at 3:46 pm

    • Hi Martijn,

      Something like this should do what you want:

      WITH
      MEMBER MEASURES.[Winners] AS
      COUNT(
      FILTER(
      NONEMPTY(
      NONEMPTY(
      [Customer].[Customer].[Customer].MEMBERS
      , [Measures].[Internet Sales Amount])
      , {([Measures].[Internet Sales Amount]
      ,[Date].[Calendar].CURRENTMEMBER.PREVMEMBER)})
      , [Measures].[Internet Sales Amount]>
      ([Measures].[Internet Sales Amount]
      ,[Date].[Calendar].CURRENTMEMBER.PREVMEMBER)
      )
      )

      SELECT
      {[Measures].[Winners]} ON 0,
      [Date].[Calendar].[Calendar Year].MEMBERS ON 1
      FROM [Adventure Works]

      Here, the Winners measure finds the number of customers who have bought something in the current time period and the previous time period, and who bought more in the current time period than in the previous time period.

      Chris Webb

      July 28, 2011 at 9:00 pm

  8. Works like a charm! Thanks for your fast response.

    Sandbergen

    August 1, 2011 at 10:10 am

  9. Hi Chris, found your post and it fits very well with the problem I’m facing now.
    Let’s say you want to get the Internet Sales Amount for the Returning Customers. I have used your code to create a calculated set in the cube and then use this to aggregate with Internet Sales, but the value returned is NULL (empty / blank) for every Calendar period. Don’t know what I’m missing.

    Julio Acuna

    September 11, 2011 at 11:06 am

    • can you post your code?

      Chris Webb

      September 11, 2011 at 8:04 pm

      • For the set:

        CREATE DYNAMIC SET CURRENTCUBE.[Returning Customers]
        AS NONEMPTY(
        NONEMPTY(
        [Customer].[Customer].[Customer].MEMBERS
        , [Measures].[Internet Sales Amount])
        , {[Measures].[Internet Sales Amount]}
        * {NULL : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER}
        ), DISPLAY_FOLDER = ‘Sets’ ;

        For the calculated member:

        CREATE MEMBER CURRENTCUBE.[Measures].[Returning Customer Sales]
        AS AGGREGATE ([Returning Customers],[Measures].[Internet Sales Amount]),
        FORMAT_STRING = “Currency”,
        VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’ ;

        Julio Acuna

        September 12, 2011 at 1:10 am

      • The problem here is that you can’t use a named set like this. A named set, even if it is marked as dynamic, will only ever be evaluated once per query – you need to insert the set expression directly inside your calculated measure, so it gets evaluated each time the calculation gets called (as I’ve done in the post).

        Chris Webb

        September 12, 2011 at 9:19 am

  10. Ok, thanks for your response

    Julio Acuna

    September 12, 2011 at 9:30 am

  11. Hi Chris,
    I am working on a slightly different scenario than the one who explained above;
    is it possible to show the returning customer for every time period? For example users want to know the returning customers in Dec who also purchased in August, etc. It is possible to modify your code to answer this specific question but can this be made more generic calculated member so users can see across time period who the returning customers are.
    Thanks,
    Vamsi.

    Vamsi

    November 28, 2011 at 4:11 pm

    • Yes, it’s certainly possible to show the returning customers but you wouldn’t use a calculated member for this. Instead of counting the number of items in the set, you display the set’s contents on the rows axis of the query. So something like this (I don’t have access to SSAS at the moment so I can’t test the query, but you get the idea) which shows the list of customers who bought in 2004 and all years before:

      SELECT
      {[Measures].[Internet Sales Amount]} ON 0,
      NONEMPTY(
      NONEMPTY(
      [Customer].[Customer].[Customer].MEMBERS
      , ([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2004]))
      , {[Measures].[Internet Sales Amount]} * {NULL : [Date].[Calendar Year].&[2003]})
      ON 1
      FROM [Adventure Works]

      Chris Webb

      November 29, 2011 at 8:26 am

      • Chris,
        Thanks for the fast response. I am sorry I was not clear in my problem description.
        your query works and it brings out the customers who are returning (from beginning of time to 2003) customers for 2004. Is it possible to break this returning customers for 2004 into each year;
        customers who are returning in 2004 and made purchases in 2003
        customers who are returning in 2004 and made purchases in 2002
        customers who are returning in 2004 and made purchases in 2001
        customers who are returning in 2004 and made purchases in 2000
        etc..to beginning of time.
        this could mean that the same customer can repeat multiple times if they made purchases in multiple years.
        Thanks,
        Vamsi.

        Vamsi

        November 29, 2011 at 3:40 pm

      • So, on rows, you’d want to see a list of years and then crossjoined with each year you’d see a list of the customers who made purchases in that year and are returning in 2004?

        Chris Webb

        November 29, 2011 at 4:19 pm

      • Yes.

        Vamsi

        November 29, 2011 at 4:21 pm

      • Here you go:

        SELECT
        {[Measures].[Internet Sales Amount]} ON 0,
        GENERATE(
        [Date].[Calendar Year].[Calendar Year].MEMBERS
        ,
        {[Date].[Calendar Year].CURRENTMEMBER}
        *
        NONEMPTY(
        NONEMPTY(
        [Customer].[Customer].[Customer].MEMBERS
        , ([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2004]))
        , ([Measures].[Internet Sales Amount]))
        )
        ON 1
        FROM [Adventure Works]

        Chris Webb

        November 29, 2011 at 4:39 pm

  12. Hi Chris,

    (sorry for my poor english)
    I’ve got a similar scenario for as customer of us who wants to get insights in new, returning en lost customers i a selected period. However, this customer browse the cube with excel 2010. Is it possible to configure the cube where you count for example the lost customers for a selected period, and then put the customer City on the rows?

    When i try to make calculated measures of above suggestions, then there is no relation with the customer dimension.

    Do you have any suggestions which lead me to the right approach?

    Rgds, Wilfred

    Wilfred Vonk

    March 6, 2012 at 3:53 pm

    • add Existing before .Members

      QQ

      March 6, 2012 at 4:19 pm

    • You’d need to do something like create two extra time dimensions (with no relationship with any measure group) so users could define their selected period for comparison, and then reference that selection inside your calculation. It’s feasible but I can’t think of an example online I can reference easily.

      Chris Webb

      March 6, 2012 at 9:06 pm

  13. Hi Chris,
    Thanks for above demo. I have a question. I am trying to create a calculated measure that display count of Customer that but Product A and Product B. So I created the a measure A and Measure B (Sales Amount). When I add the both measures together this will give me the list of customers with there sales amount. Instead of displaying sales amount I want to display 1. So when I browse the cube It will roll up. Below is the current code I am using

    with

    member measures.[A] as
    (
    [Product].[Product Categories].[Product].[Mountain-200 Silver, 38],
    [Measures].[Internet Sales Amount]
    )

    member measures.[B] as
    (
    [Product].[Product Categories].[Product].[Mountain-200 Black, 42],
    [Measures].[Internet Sales Amount]
    )

    member measures.[C] as
    measures.[A] + measures.[B]

    member measures.[D] as
    count (
    nonempty (
    [Customer].[Customer].[Customer].members,
    measures.[C]
    )
    )

    //Mountain-200 Black, 42
    select
    //nonempty (Measures].[Internet Sales Amount], measures.A, measures.B, measures.C,
    { measures.D}
    //)
    on 0,
    non empty (
    {[Date].[Calendar Year].members}
    //{[Customer].[Customer].[Customer].members}
    )
    on 1
    from [Adventure Works]

    The above code works well if I don’t display customer on row axis. But I will like to customer on the row too (the query is very slow and it display the same value for all the customers in mine dimension).

    Please do you have any suggestion.

    Thanks,
    Femi

    Femi

    April 3, 2012 at 5:04 pm

    • Hi Femi,

      Does this version of measures.D do what you want?

      member measures.[D] as
      count (
      nonempty (
      existing [Customer].[Customer].[Customer].members,
      –measures.[C]
      {[Measures].[Internet Sales Amount]} *
      {[Product].[Product Categories].[Product].[Mountain-200 Silver, 38],
      [Product].[Product Categories].[Product].[Mountain-200 Black, 42]}
      )
      )

      Chris Webb

      April 4, 2012 at 10:28 pm

  14. Hi Chris

    Thanks for your post, It has been very helpful.

    I’m currently working on slightly similar case like the one that you have described in your post.

    What I need to archive is to create new calculated measures based on the status of Contract. The status attribute can change within a month and the contract dimension is SCD type II. Contract dimension table has start and end dates for tracking the attribute changes.
    I need to be able to report how many Contracts are open, new or closed based on the status of the contract within time. Reporting happens at month level so I need to report my Contracts based on the status at the end of each the month. Also I need to report the number of contracts that have bought something from us based on the sales measure in month level (Active contracts and Passive contracts)
    Any suggestions?

    Best Regards, Jask ma

    jask ma

    April 19, 2012 at 10:49 am

  15. Hi Chris,

    I’m pretty new to MDX. I saw one of your post back on Nov. 28, 2011 about returning customer for every time period. My scenario is to return any customer who purchased in previous year, but did not purchase current year. I tried to test your code and use except and isempty, but not successful. I would be greatly appreciated if you can modify the following code that you posted last year to show customers who did not purchase in 2004.

    SELECT
    {[Measures].[Internet Sales Amount]} ON 0,
    NONEMPTY(
    NONEMPTY(
    [Customer].[Customer].[Customer].MEMBERS
    , ([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2004]))
    , {[Measures].[Internet Sales Amount]} * {NULL : [Date].[Calendar Year].&[2003]})
    ON 1
    FROM [Adventure Works]

    Thanks,
    Jocelyn Lau

    Jocelyn Lau

    June 30, 2012 at 8:25 pm

    • Here you go, this returns customers who bought in 2003 but not in 2004:

      SELECT
      {[Measures].[Internet Sales Amount]} ON 0,
      EXCEPT(
      NONEMPTY(
      [Customer].[Customer].[Customer].MEMBERS
      , ([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2003]))
      ,
      NONEMPTY(
      [Customer].[Customer].[Customer].MEMBERS
      , ([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2004]))
      )
      ON 1
      FROM [Adventure Works]

      Chris Webb

      July 1, 2012 at 9:28 pm

      • Chris,

        Thank you so much for your help!

        Jocelyn Lau

        Jocelyn Lau

        July 2, 2012 at 1:37 am

  16. Hi Chris,

    Hope you’re well. Very cool blog! I’ll need to spend alot of time reading some of the articles. I’ve only started using SSAS this year, so may be a little green in some areas. I’ve managed to create a cube and put some calculations in it, but the one that I am struggling with is a the sum of the customer count over a period of time that counts only distinct customers. I have a look at your above and have tried to get it to work for me with minimal luck.

    Just the first question as this seems to be something that I might have just missed the point on, but this section of code that you have (which I’ve adapted slightly for my project), does it go in the Calculations as calculated member?

    I have the below in my cube from your example

    CREATE MEMBER CURRENTCUBE.[Measures].[TestCustomerCount]
    AS
    DISTINCTCOUNT([Customers].[Customer Code].[Customer Code].Members),
    VISIBLE = 1, DISPLAY_FOLDER = ‘Test';

    WITH
    MEMBER [MEASURES].[Returning Customers] AS
    COUNT(
    NONEMPTY(
    NONEMPTY(
    [Customers].[Customer Code].[Customer Code].MEMBERS
    , [Measures].[Value USD])
    , {[Measures].[Value USD]}
    * {NULL : [Dates].[CalendarHeirarchy].CURRENTMEMBER.PREVMEMBER}
    )
    )
    MEMBER MEASURES.[New Customers] AS
    [Measures].[TestCustomerCount] – [MEASURES].[Returning Customers]
    SELECT
    {[Measures].[Customer Count]
    , MEASURES.[Returning Customers]
    , MEASURES.[New Customers]} ON 0,
    [Date].[CalendarHeirarchy].[Date Year].MEMBERS ON 1
    FROM [TestDatabase];

    If I process my cube I get – Error 8 Parser: The script contains the statement, which is not allowed.

    Then the main reason that I am trying all this is that I need to get a list of the distinct customers that purchased over a 12mm moving period, I seem to have sorted out the 12mm using the Time Intelligence Wizard, which works on all my other calculated members, but not on this one as it’s not additive.

    To go over a simple situation, if the following customers purchased in the following months:
    Jan 12 – A, B, C, D, E (5 customers)
    Feb 12 – A, B (2 customers)
    Mar 12 – E, F, G (3 customers)

    Now I want a calculated member that will be able to return 7 (i.e. there were 7 distinct customers in the period, A, B, C, D, E, F, G)

    Thanks for your time and effort, and congrats on becoming an SSAS Maestro (may be a delayed congrats)

    Regards
    Mark

    Mark

    July 11, 2012 at 8:15 am

    • I see a syntax error in the code that I put up there, in the bottom SELECT statement I used [Measures].[Customer Count] instead of [Measures].[TestCustomerCount]. I’ve made the update here, but there is still the same error when processing.

      Mark

      July 11, 2012 at 8:24 am

      • Hi Mark,

        In your example, everything from WITH MEMBER to the end is an MDX query, so it is something that can only be executed in SQL Server Management Studio (or via code). It’s only the CREATE MEMBER statement before it that can go on the Calculations tab on the cube.

        Chris

        Chris Webb

        July 11, 2012 at 2:43 pm

    • Hi Chris,

      Thanks for your feedback and explaining which parts of it were MDX and which were in the Calculations tab. I’ve got that setup now and can run the query which returns the results.

      This wasn’t really what I was aiming to achieve, but was some extra insight into SSAS. I’m still stuck with my last question of the distinct customers. This also preferably needs to be done in the calculations tab, as we’re delivering this onto an XLS sheet which the calculations can be dragged nicely onto the pivot table where the MDX is abit of a problem to get on there.

      I think one of the main issues that I have with the distinct customers is that I can get a list of the distinct customers easily which is sliced by a selection period that is selected as the filter, however the 12 month moving thing that I have created is as per below

      CREATE MEMBER CURRENTCUBE.[Dates].[12mm].[12mm LY] AS “NA” ;
      // 12mm LY
      (
      [Dates].[12mm].[12mm LY],
      [Dates].[MMM YY].[MMM YY].Members,
      [Dates].[Date ID].Members,
      {
      [Measures].[AverageExchangeRate],
      [Measures].[AverageRSP-LC],
      [Measures].[AverageRSP-USD],
      [Measures].[AverageOrderValue-LC],
      [Measures].[AverageOrderValue-USD],
      [Measures].[TotalProductCount],
      [Measures].[ProductCount],
      [Measures].[ProductCountPercent],
      [Measures].[AverageOrderUnits],
      [Measures].[ProductAvailability],
      [Measures].[CustomerSalesRatioToParent-LC],
      [Measures].[CustomerSalesRatioToParent-USD],
      [Measures].[CustomerSalesRatioToParent-Units],
      [Measures].[CustomerSalesRatioToParent-Tons],
      [Measures].[CustomerSalesRatioToParent-KGs],
      [Measures].[ProductSalesRatioToParent-LC],
      [Measures].[ProductSalesRatioToParent-USD],
      [Measures].[ProductSalesRatioToParent-Units],
      [Measures].[ProductSalesRatioToParent-Tons],
      [Measures].[ProductSalesRatioToParent-KGs],
      [Measures].[TotalCustomerCount],
      [Measures].[CustomerCount],
      [Measures].[CustomerCountPercent],
      [Measures].[Units],
      [Measures].[Value LC],
      [Measures].[Value USD],
      [Measures].[KGs],
      [Measures].[Tons],
      [Measures].[Orders],
      [Measures].[PricePerTon-LC],
      [Measures].[PricePerTon-USD]
      }

      )

      =

      AGGREGATE(
      ParallelPeriod(
      [Dates].[CalendarHeirarchy].[MMM YY],
      23,
      [Dates].[CalendarHeirarchy].CurrentMember
      )
      :

      ParallelPeriod(
      [Dates].[CalendarHeirarchy].[MMM YY],
      12,
      [Dates].[CalendarHeirarchy].CurrentMember
      )
      *
      [Dates].[12mm].[Current Dates]
      ) ;

      CREATE MEMBER CURRENTCUBE.[Dates].[12mm].[12mm Growth]
      AS ([Dates].[12mm].[12mm CY] – [Dates].[12mm].[12mm LY]) / [Dates].[12mm].[12mm LY] ;

      // YearAgoMonth
      (
      [Dates].[12mm].[YearAgoMonth],
      [Dates].[MMM YY].[MMM YY].Members,
      [Dates].[Date ID].Members,
      {
      [Measures].[AverageExchangeRate],
      [Measures].[AverageRSP-LC],
      [Measures].[AverageRSP-USD],
      [Measures].[AverageOrderValue-LC],
      [Measures].[AverageOrderValue-USD],
      [Measures].[TotalProductCount],
      [Measures].[ProductCount], <- doesn't work
      [Measures].[CustomerCount], <- doesn't work
      }

      )

      =

      AGGREGATE(
      ParallelPeriod(
      [Dates].[CalendarHeirarchy].[MMM YY],
      12,
      [Dates].[CalendarHeirarchy].CurrentMember
      )
      :

      ParallelPeriod(
      [Dates].[CalendarHeirarchy].[MMM YY],
      12,
      [Dates].[CalendarHeirarchy].CurrentMember
      )
      *
      [Dates].[12mm].[Current Dates]
      ) ;

      Mark

      July 12, 2012 at 7:47 am

  17. Hi Chris,

    I’m new to mdx and I have a problem similar to the lost customers query you have demonstrated.

    Here is my scenario. I have a cube designed to track the hiring process of an applicant for a job. So, they go from applying, to hired, and sometimes to termination.

    I want to find the number of employees that are hired in a particular month and see how many of these same employees are still around in each month after.

    So 10 employees were hired in July 2012, I want to get the number that have not been terminated in August 2012 and so forth.

    July 2012 Hired August 2012 Sept 2012
    10 9 remain 7 remain

    My measures are [Hired Qty] and [Termination Qty].
    I have role playing dimensions for date: [Hire Date] and [Termination Date].

    I was thinking I need to get the Hired Month as a set and then subtract out the Termination Qty for each month after by the Termination Date dimension.

    Thanks.
    Brock

    Brock

    August 31, 2012 at 6:26 pm

    • Hi Brock,

      This is going to be more of a cube design problem than an MDX problem: once you’ve designed your cube appropriately the MDX should be straightforward (and conversely, the MDX will be horrible if your cube isn’t designed appropriately!). Can you give me some idea of what your fact table looks like?

      Chris

      Chris Webb

      September 6, 2012 at 2:02 pm

      • Sure, the fact table is setup as an accumulating snapshot of the hiring process for an employee.

        CREATE TABLE [Hiring].[factCandidatePipeline](
        [dimCandidateKey] [int] NOT NULL,
        [dimEmployeeKey] [int] NOT NULL,
        [dimApplicationStartDateKey] [int] NOT NULL,
        [dimReviewedDateKey] [int] NOT NULL,
        [dimInterviewDateKey] [int] NOT NULL,
        [dimNotInterestedDateKey] [int] NOT NULL,
        [dimBackgroundCheckRequestDateKey] [int] NOT NULL,
        [dimBackgroundCheckResponseDateKey] [int] NOT NULL,
        [dimHiredDateKey] [int] NOT NULL,
        [dimEmployeeStartDateKey] [int] NOT NULL,
        [dimTerminationDateKey] [int] NOT NULL,
        [dimOnboardingStartDateKey] [int] NOT NULL,
        [dimOnboardingCompleteDateKey] [int] NOT NULL,
        [ApplicantQty] [tinyint] NOT NULL,
        [ApplicationCompletedQty] [tinyint] NOT NULL,
        [HiredQty] [tinyint] NOT NULL,
        [ApplyHiredLagDays] [int] NULL,
        [TerminationQty] [tinyint] NOT NULL,
        [HiredTerminationLagDays] [int] NULL,
        [BackgroundCheckRequestQty] [tinyint] NOT NULL,
        [ApplyBackgroundCheckRequestLagDays] [int] NULL,
        [BackgroundCheckResponseQty] [tinyint] NOT NULL,
        [BackgroundCheckRequestResponseLagDays] [int] NULL,
        [OnboardingStartQty] [tinyint] NOT NULL,
        [ApplyOnboardingStartLagDays] [int] NULL,
        [OnboardingCompleteQty] [tinyint] NOT NULL,
        [ApplyOnboardingCompleteLagDays] [int] NULL,
        [EmployeeStartOnboardingCompleteLagDays] [int] NULL,
        [ReviewedQty] [tinyint] NOT NULL,
        [ApplyReviewedLagDays] [int] NULL,
        [InterviewQty] [tinyint] NOT NULL,
        [ApplyInterviewLagDays] [int] NULL,
        [NotInterestedQty] [tinyint] NOT NULL,
        [ApplyNotInterestedLagDays] [int] NULL,
        [DropOffQty] [tinyint] NOT NULL)

        Brock

        September 6, 2012 at 2:31 pm

      • OK, in that case it should be fairly straightforward. You create a calculated measure that takes the number of hires measure for all termination dates, and then subtracts the sum of terminations from the beginning of time up to the current termination date. You can then select your hire date and then put termination date on rows or columns and see how the number of hires still employed reduces over time. The MDX would be something like this:
        (Measures.[Hires], [Termination Date].[Calendar].[All]) – SUM(null:[Termination Date].[Calendar].currentmember, Measures.[Terminations])

        Chris

        Chris Webb

        September 6, 2012 at 2:46 pm

      • This doesn’t seem to give me what I want. I want to take the employees that were hired in January 2012 and see how many of those same employees from January 2012 remain in February 2012.

        When you said take “select your hired date”, did you mean to put hire date on one axis and the terminated date on the other axis?

        Brock

        September 6, 2012 at 6:38 pm

      • Sorry not to be clear – I meant to say you could put Hire Date on the filter axis and then put Termination Date on rows or columns; this should ensure you slice so that you only get the people hired in a particular month.

        Chris Webb

        September 7, 2012 at 9:22 am

      • Thanks, this seems to be working when browsing with Excel when I pick 1 month of the Hire Date calendar. I have a couple of questions though.

        Why does this not work when browsing with SSMS?

        Also, do you think it would be possible to put the Hire Date dimension on 1 axis and the Termination Date on another axis so I can see this for multiple Hire date months?

        Brock

        September 7, 2012 at 2:57 pm

      • Thanks a bunch Chris. Putting Hire Date on X axis and Termination Date the Y axis works in Excel. I’ve been trying to figure this out for 2 weeks.

        I still don’t understand why the calculation doesn’t work in SSMS.

        Since we will be using Excel for our front end I guess we don’t have a big problem.

        Brock

        September 7, 2012 at 3:41 pm

      • It might not be working in SSMS because you’re putting the Hire Date dimension in the filter pane at the very top; it should work if you drag it into the relatively think section marked (from memory) ‘Drop Filter Fields Here’.

        Chris Webb

        September 7, 2012 at 4:14 pm

  18. Thanks, will give this a try.

    Brock

    September 6, 2012 at 5:51 pm

    • Hi,
      I’m struggling with a slightly different problem and I’m stuck, so I’m searching for some advises. I have customers dimension, statuses dimension (let’s put for simplicity just 2 statuses) and dates. In fact table I keep history of status changes for every customer, so, for example, when a customer enters the database he/she is in “status_1″, after some time, the customer may change his status to “status_2″ and I add one more record to fact table with date; and after that customer may again return to “status_1″ – one more record in fact table again. These “jumps” between statuses occur not often than once a day per customer.
      Now, I cannot figure out how to do a report about customers database with respect to latest known status for a given date.
      Suppose we have only one customer in our database. 2012-01-01 this customer was in “status_1″, so we have one record in fact table; 2012-03-01 he changed status to “status_2″ – we add another record; and, finally, 2012-05-01 he moved back to “status_1″. I do a report, and my reporting date is 2012-02-01. I see 1 customer in my database in “status_1″, 0 customers in “status_2″. When my reporting date is 2012-04-01, then I see 0 customers for “status_1″ and 1 customer in “status_2″. And, when report is done for 2012-06-01, I again see 1 customer in “status_1″ and 0 customers in “status_2″.
      With pure SQL and given data model I would solve the problem with few lines of code. like
      SELECT
      t.[last_status]
      ,COUNT(t.[customer_id])
      FROM (
      SELECT
      r.[customer_id]
      ,(SELECT TOP 1 l.[status_id]
      FROM [dwh].[dbo].[fact_customers_statuses] l
      WHERE l.[customer_id] = r.[customer_id] AND l.status_id IN (1, 2) AND l.status_day_date < '2012-02-01'
      ORDER BY l.status_day_date DESC) AS [last_status]
      FROM (
      SELECT DISTINCT
      f.[customer_id]
      FROM [dwh].[dbo].[fact_customers_statuses] f
      WHERE
      f.status_day_date <= '2012-02-01' AND f.status_id IN (1, 2)) AS r) AS t
      WHERE
      t.[last_status] IS NOT NULL
      GROUP BY
      t.[last_status]
      Works in less than a second!

      I have no idea how to do equivalent in MDX. the task seems to be simple, but it's not. At least for me.
      What I got so far, is I can obtain the latest status per each customer for a given date (t query in my SQL code). It is extremely slow – 5 minutes.
      WITH
      Member Measures.[Last Status] AS
      NonEmpty
      ([Customer Statuses].[Id].[Id]
      ,(Tail(NonEmpty({NULL:Tail(Descendants([Customer Statuses Dates].[Day Date].CurrentMember, [Customer Statuses Dates].[Day Date].[Day Date])).Item(0)}
      ,[Measures].[Customers Count]
      )),[Measures].[Customers Count]
      )).Item(0).Name
      SELECT
      NON EMPTY [Customer Statuses Dates].[Day Date].&[2012-01-01T00:00:00] * {Measures.[Last Status]} ON 0,
      NON EMPTY {[Customers].[Id].[Id]} on 1
      FROM [Applications]
      And I do not know what to do next. How to perform counts now?
      Please, help!

      Oleksandr

      December 14, 2012 at 2:49 pm

      • Hi Oleksandr,

        This is exactly the scenario I’m describing in this blog post. Instead of treating Status like a dimension, treat it like a measure (ie return the last non-empty Status key value) and the technique above will work. All you need to do then is use another calculated member to translate the key value into a readable status.

        Chris

        Chris Webb

        December 17, 2012 at 9:34 am

      • Hi Chris,
        Yes, I understand the idea. My problem is that I know what to do, but I do not know how. I come from pure SQL world, and there are no “attributes” or “measures” entities, because everything can be turned into kind of attribute or measure. For me SSAS is a bit “black and white” yet, but I think it’s a matter of experience and understanding what is going on behind the scene. I’ll keep trying, also with another your post about Last ever non empty, which might be useful in this case as well.

        Oleksandr

        December 19, 2012 at 8:01 am

      • Following your post about Last ever non empty, I created a fake measure [Maximum Date] in my fact table. then I added script to my cube as following
        CREATE MEMBER CURRENTCUBE.[Measures].[Days To Date]
        AS COUNT(NULL:[Customer Statuses Dates].[Day Date].CurrentMember) – 1,
        VISIBLE = 0;
        CREATE MEMBER CURRENTCUBE.[Measures].[Customer Had Status]
        AS IIF([Measures].[Customers Count] = 0, NULL, [Measures].[Days To Date]),
        VISIBLE = 0;
        //—-
        SCOPE([Measures].[Maximum Date]
        , [Customer Statuses Dates].[Day Date].[Day Date].Members
        );
        This = MAX(
        {NULL:[Customer Statuses Dates].[Day Date].CurrentMember}
        , [Measures].[Customer Had Status]);
        END SCOPE;
        I also added [Last Status Id] measure to my fact table (It could be Max or Sum, doesn’t matter). Now, combining [Maximum Date] and [Last Status Id] I can get LAST_AVAILABLE_STATUS for any reporting date for any existing on that date customer like following
        MEMBER MEASURES.LAST_AVAILABLE_STATUS AS
        IIF(ISEMPTY(MEASURES.MAXDATE), NULL,
        ([Measures].[Last Status Id], [Customer Statuses Dates].[Day Date].[Day Date].MEMBERS.ITEM(MEASURES.[Maximum Date])))
        How do I perform count of the customers by this last available status?

        Oleksandr

        December 19, 2012 at 2:52 pm

      • Can you tell me what you mean when you say you want the count of customers by last available status? Does the Last Available Status measure return a key value of some sort?

        Chris Webb

        January 2, 2013 at 1:03 pm

      • Yes, this is “status_id” key. Now, I want to count the customers according to this “status_id” key, and group them by status name.
        Actually, I solved this problem by another way, by augmenting my fact table. Every time a customer changes status, I add record like “day_date:2012-03-01, customer_id:1, status_id:2, status_sign:1″. At the same time I add record to denote that a customer changed status from 1 to 2 by adding a record “day_date:2012-03-01, customer_id:1, status_id:1, status_sign:-1″. Then, my measure is just a simple sum of “status_sign” and that’s it!

        Oleksandr

        January 2, 2013 at 1:17 pm

      • That sounds like a better solution – any MDX approach is always going to perform worse than this.

        Chris Webb

        January 2, 2013 at 1:18 pm

  19. Yes, speed is awesome! anyway, thank you for your time! Happy New Year!

    Oleksandr

    January 2, 2013 at 1:24 pm

    • Hi All

      Can anyone help me out in this.

      In my database i have stored value for new customer in cache table using below query.

      But i want to do the same in SSAS without using cache table.

      Here senario is i want count of customers present in current date but not present in previous date(New customers).

      For e.g. I want count of customers present in ‘2013-01-05′ but not present in ‘2013-01-04′ then present in ‘2013-01-04′ but not in ‘2013-01-03′ so on…

      insert into tb_Date
      select distinct Date,Freq_ID from Tb_Customer
      order by Date desc

      Declare @count int,@CountEnd int

      set @count = 1
      set @CountEnd = (select count (1) from tb_Date)

      Declare @currnt_date datetime
      Declare @compr_date datetime

      while (@count < @CountEnd)
      begin

      set @currnt_date = (select Date from tb_Date where ID = @count )
      set @compr_date = (select top 1 Date from tb_Date where Date < @currnt_date)

      insert into New_Customer_Count
      select COUNT(Distinct Customer_id) as Total_Count
      from Tb_Customer A
      where Date = @currnt_date
      and Customer_id not in
      (
      select Customer_id from Tb_Customer B
      where Date = @compr_date
      )
      set @count = @count + 1

      end

      thanks in advance

      Regards,

      Swapnil

      Swapnil

      February 15, 2013 at 12:00 pm

      • Hi Swapnil,

        Read the blog post, that’s the problem it explains how to solve…!

        Chris

        Chris Webb

        February 15, 2013 at 12:34 pm

  20. Hi Chris,

    I read your blog.
    But here what i need is count of customer present for a date but not present in last previous date i.e. (date – 1)…I have done this using MDX after reading your blog thanks for the same.
    But my query is too slow when i take date range for 1 year..so need to cache new customers as measure in SSAS.
    Please help in this as i am trying this from long time.

    Thanks,
    Swapnil

    Swapnil

    February 15, 2013 at 1:00 pm

  21. Hi Chris,
    Below is the MDX for the same.

    WITH
    MEMBER [MEASURES].[Returning Customers] AS
    COUNT(
    NONEMPTY(
    NONEMPTY(
    [Tb Customers].[Customer ID].members
    ,[Measures].[Customers Count] )
    , {[Measures].[Customers Count]}
    * {NULL : [Time].[Date].currentmember.PREVMEMBER}
    )
    )

    MEMBER [MEASURES].[New Customers] AS
    [Measures].[Customers Count] – [MEASURES].[Returning Customers]

    SELECT
    {[Measures].[Customers Count]
    , [MEASURES].[Returning Customers]
    , [MEASURES].[New Customers]} ON 0,
    {[Time].[Date].&[2012-11-22T00:00:00]:[Time].[Date]..&[2011-11-26T00:00:00]}ON 1
    FROM [Cube Splitting]
    where [Tb freq].[Freq ID].&[1]

    Regards,
    Swapnil

    Swapnil

    February 15, 2013 at 1:06 pm

    • I suggest you take a look at the first comment on this post, from Deepak – that contains a different approach that may perform faster for you.

      Chris Webb

      February 15, 2013 at 1:29 pm

      • Hi Chris,

        Approach given in Deepak comment is considering current date with all previous date data..here i need to consider only difference of one day…i.e. customers present for today but not present on yesterday then customer present yesterday but not present day before yesterday and so on…

        Regards,
        Swapnil

        Swapnil

        February 15, 2013 at 2:48 pm

      • Here’s Deepak’s query adapted to show new customers on a daily basis:

        WITH
        MEMBER MEASURES.[Customers Today and Yesterday] AS
        Aggregate({[Date].[Calendar].CURRENTMEMBER.PREVMEMBER:[Date].[Calendar].CURRENTMEMBER}
        , [Measures].[Customer Count])

        MEMBER MEASURES.[Customers Yesterday] AS
        ([Date].[Calendar].CURRENTMEMBER.PREVMEMBER, [Measures].[Customer Count])

        MEMBER MEASURES.[New Customers] AS
        MEASURES.[Customers Today and Yesterday]

        MEASURES.[Customers Yesterday]
        SELECT
        {[Measures].[Customer Count]
        , MEASURES.[Customers Yesterday]
        , MEASURES.[Customers Today and Yesterday]
        , MEASURES.[New Customers]} ON 0,
        FILTER(
        [Date].[Calendar].[Date].MEMBERS
        , [Measures].[Customer Count]MEASURES.[New Customers])
        ON 1
        FROM [Adventure Works]

        Chris Webb

        February 15, 2013 at 8:54 pm

  22. Hi Chris,
    Thank you for your help.

    Regards,
    Swapnil

    Swapnil

    February 21, 2013 at 5:35 pm

  23. [...] been addressed by quite a lot of people. Chris Webb blogged about it here(PowerPivot/DAX) and here(SSAS/MDX), Javier Guillén here, Alberto Ferrari mentions it in his video here and also [...]

  24. I’ve tried, unsuccessfully, to modify this to do what I’m looking for: show the number of “consistent” customers from “the start of time” until now. Consistent would mean that they have purchased in each of the time periods until now. Logically, the value could not exceed the max of the number of customers that purchased in the first time period. I think the listed example does this, but only for pairs of periods: “how many bought in this period who also bought in the last period”. Is there a way to get what I’m looking for? All of the approaches that I have considered lead me to guess that I need something recursive, but I suspect that isn’t the MDX way.

    Sohrab Niramwalla (@scrampy)

    February 23, 2013 at 7:43 pm

  25. [...] post I wrote a few years ago on counting new and returning customers in MDX has proved to be one of the most popular here; it’s certainly a topic I’ve returned to a number [...]

  26. HI Chris,

    Can you please help me out in writing MDX query for self join.

    Below is my scenario
    Here i need only those records which matches with ‘SKU’ of Owner ‘A’.

    SQL query for the same is:

    Select * from table_A Z
    inner join (select SKU from table_A where Owner = ‘A’) M
    on Z.sku = M.sku

    Table A
    ID Product_ID Owner SKU
    1 123 A 12345
    2 23 A 22222
    3 324 A 9999
    4 423 B 12345
    5 324 B 9999
    6 3 B 34456
    7 4 B 54667
    8 2 C 75675
    9 3 C 12345
    10 4 C 22222

    OUTPUT
    ID Product_ID Owner SKU
    1 123 A 12345
    2 23 A 22222
    3 324 A 9999
    4 423 B 12345
    5 324 B 9999
    9 3 C 12345
    10 4 C 22222

    Thanks,
    Swapnil

    swapnil018

    March 4, 2013 at 12:44 pm

  27. HI Chris,

    Can you please help me out in writing MDX query for self join.

    Below is my scenario
    Here i need only those records which matches with ‘SKU’ of Owner ‘A’.

    SQL query for the same is:

    Select * from table_A Z
    inner join (select SKU from table_A where Owner = ‘A’) M
    on Z.sku = M.sku

    Table A
    ID Product_ID Owner SKU
    1 123 A 12345
    2 23 A 22222
    3 324 A 9999
    4 423 B 12345
    5 324 B 9999
    6 3 B 34456
    7 4 B 54667
    8 2 C 75675
    9 3 C 12345
    10 4 C 22222

    OUTPUT
    ID Product_ID Owner SKU
    1 123 A 12345
    2 23 A 22222
    3 324 A 9999
    4 423 B 12345
    5 324 B 9999
    9 3 C 12345
    10 4 C 22222

    Thanks,
    Swapnil

    Swapnil

    March 4, 2013 at 12:47 pm

    • Hi Swapnil,

      For general questions like this that aren’t related to a specific post, you’re better off posting your question on the MSDN forum (http://social.msdn.microsoft.com/forums/en-us/sqlanalysisservices/threads/).

      To answer your question though, here’s an example from Adventure Works. To start with, here’s a query that shows the sales of all products (to all customers) that have been bought by the customer Aaron A. Hayes:

      select {[Measures].[Internet Sales Amount]} on 0,
      nonempty(
      [Product].[Product].[Product].members
      , {([Measures].[Internet Sales Amount]
      , [Customer].[Customer].&[15568])})
      on 1
      from [Adventure Works]

      Here’s what I think you want though, a query that shows the names of all the customers who bought the same products as Aaron A. Hayes:

      select {[Measures].[Internet Sales Amount]} on 0,
      non empty
      nonempty(
      [Product].[Product].[Product].members
      , {([Measures].[Internet Sales Amount]
      , [Customer].[Customer].&[15568])})
      *
      [Customer].[Customer].[Customer].members
      on 1
      from [Adventure Works]

      Chris Webb

      March 4, 2013 at 7:59 pm

      • Hi Chirs,

        Sure. Next time onwords i willl post new topics in msdn.

        Here is my aproch.

        with
        set [aa] as

        filter(
        except([Product].[sku].members,[Product].[sku].[all]),
        [customer].[customerId].&[2])*[Measures].[Internet Sales Amount]

        SELECT
        NON EMPTY (NONEMPTY({[customer].[customer Name].&[A]})) on columns,

        NON EMPTY(NONEMPTY({[aa]})) ON rows

        FROM [cube]

        WHERE
        (
        {[city].[city id].&[1]}
        )

        Above MDx gives me correct out put as below.

        A
        13186139 Internet Sales Amount 182.91
        13189678 Internet Sales Amount 180.07
        13348448 Internet Sales Amount 578.11
        13348695 Internet Sales Amount 1349.95
        13348696 Internet Sales Amount 1596.51
        13442983 Internet Sales Amount 44.99
        13721556 Internet Sales Amount 11.99
        13818055 Internet Sales Amount 733.22
        13818368 Internet Sales Amount 810.59
        13818370 Internet Sales Amount 826.72
        13818376 Internet Sales Amount 1526.86
        13818391 Internet Sales Amount 744.58
        13818393 Internet Sales Amount 763.86
        13818394 Internet Sales Amount 1041.76

        But what i need is to sum (Internet Sales Amount) out of the select statement only and the display the records.

        Correct output will be:

        A
        Internet Sales Amount 10209.21

        Swapnil

        March 5, 2013 at 10:44 am

  28. hi ;
    my mdx query not worked ,i want a output Count_enqueteur in the column and connexion_soned,
    connexion_onas ,connexion_steg,satisfaction_logement,gouvernorat in row, but don’t now wat is the problem he desplayed me:
    ” [Measures].[Count_enqueteur]‘ not found in cube ‘Analyse’ ”
    note please: the measure Count_enqueteur is created.

    SELECT
    {[Measures].[Count_enqueteur]} on COLUMNS,
    {connexion_soned.children,
    connexion_onas.children ,
    connexion_steg.children,
    satisfaction_logement.children,
    gouvernorat.children} on rows
    FROM Analyse

    can you give me a help.

    zizou

    April 26, 2013 at 5:16 pm

    • Are you connected to the right database in your MDX query window? Can you try dragging the measure from the metadata pane in the MDX query window into the query pane and seeing if the uniquename that appears is the same as the one you’re using?

      Chris Webb

      April 27, 2013 at 3:51 pm

  29. Tangential question:
    How can I calculated the #unique customers who placed orders based on their status?
    e.g. my Customers table/dimension has an Account Status field with values ‘New’ or ‘Verified’ (amongst others)
    my distinct count shows the #unique customers who placed orders, but I want to know how many unique ‘Verified’ customers placed orders, and similarly how many ‘New’ customers placed orders?
    (as a calculated member in my cube – the example below is just an mdx query to test)

    I tried doing something like this but I get an infinite recursion detected:

    WITH MEMBER [Measures].[UniqueVerifiedCustomers]
    AS Count(nonempty(filter([Customers].[Customer Id].[Customer Id], [Customers].[Account Status] = “Verified”),
    [Measures].[#Orders])),
    VISIBLE = 1
    SELECT
    {
    [Measures].[UniqueVerifiedCustomers],
    [Measures].[#UniqueCustomers], — this is my distinct count on orders’ customerid
    [Measures].[#Orders] — count of rows
    } on columns
    FROM [Orders]

    JT

    May 2, 2013 at 4:15 pm

    • Sorry for the late reply… What you need is a tuple in your calculated member, something like this:

      WITH MEMBER [Measures].[UniqueVerifiedCustomers]
      AS ([Customers].[Account Status].&[Verified],
      [Measures].[#UniqueCustomers])
      SELECT …

      Chris Webb

      May 29, 2013 at 4:10 pm

  30. […] of the more popular blog posts from my archives (86 comments so far) is the one I wrote on “Counting New and Returning Customers in MDX”. The trouble with all of the calculations in there is that they execute in cell-by-cell mode, and […]

  31. […] of the more popular blog posts from my archives (86 comments so far) is the one I wrote on “Counting New and Returning Customers in MDX”. The trouble with all of the calculations in there is that they execute in cell-by-cell mode, and […]

  32. This is fantastic, thank you!!!

    Chris Ross

    June 30, 2013 at 10:41 am

  33. Very neat .impressive.

    Rajesh K Singh

    June 30, 2013 at 2:38 pm

  34. Chris,

    Excellent post. I am still fairly new to MDX and am trying to get a sum of sales from new customers. I can successfully make your example work in my data cube to get the number of new customers but cant seem to get the sum of their purchases. Could you shed a little light on that for me?

    Thanks
    Michael

    Michael S

    September 11, 2013 at 5:22 pm

    • Hi Michael,

      Does this help?

      WITH
      MEMBER MEASURES.[Returning Customers] AS
      SUM(
      NONEMPTY(
      NONEMPTY(
      [Customer].[Customer].[Customer].MEMBERS
      , [Measures].[Internet Sales Amount])
      , {[Measures].[Internet Sales Amount]}
      * {NULL : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER}
      )
      , [Measures].[Internet Sales Amount])
      MEMBER MEASURES.[New Customers] AS
      [Measures].[Internet Sales Amount] – MEASURES.[Returning Customers]
      SELECT
      {[Measures].[Internet Sales Amount]
      , MEASURES.[Returning Customers]
      , MEASURES.[New Customers]} ON 0,
      [Date].[Calendar].[Calendar Year].MEMBERS ON 1
      FROM [Adventure Works]

      Chris Webb

      September 12, 2013 at 9:46 am

  35. Hi Chris
    I have a Customer count measure as distinct count in ssas and it respects all dimensions like geography, date, products etc. This measure actually gives us the number of customers purchasing across dimensions/hierarchies. Is it possible to have a measure which will give us non-purchasing customer count which will also respect all dimensions etc.? If yes, how to create such measure in ssas?

    sham

    October 13, 2013 at 5:49 pm

  36. dear chris,
    how are you?
    i hope fine!

    do you ever tried doing this in tsql?
    regards
    Pedro

    Pedro

    December 21, 2013 at 1:25 pm

  37. Hi Chris,
    This works great for me – I’m doing a small variation for “Customers Sold Cumulative” so that by month you can see customers sold (distinct count measure) and customers sold cumulative (MDX below):

    CREATE MEMBER CurrentCube.MEASURES.[Customers Sold Cumulative] AS
    COUNT(
    NONEMPTY(
    {[Customers].[Customer ID].[Customer ID].MEMBERS}
    , {[Measures].[Dollar Sales]}
    * {NULL : [Dates].[Calendar].CURRENTMEMBER}
    ))

    Only problem is that this calculation does not “slice” by customer attributes. If you put, say, Customer Type on rows on columns, the Cumulative calculated measure repeats the same number. Same behavior on filters – customer-related filters do not change the behavior.

    Can you please offer some help?

    Dave

    June 20, 2014 at 1:23 pm

    • You probably need to add an EXISTING, like so:

      CREATE MEMBER CurrentCube.MEASURES.[Customers Sold Cumulative] AS
      COUNT(
      EXISTING
      NONEMPTY(
      {[Customers].[Customer ID].[Customer ID].MEMBERS}
      , {[Measures].[Dollar Sales]}
      * {NULL : [Dates].[Calendar].CURRENTMEMBER}
      ))

      Chris Webb

      June 20, 2014 at 1:25 pm

      • Thanks Chris, worked perfectly. You’re the best!!

        Dave

        June 23, 2014 at 5:07 pm

  38. hi Chris, I am new to MDX and trying to get customer sales for given date range (for example 2001.07.24 – 2007-08-01) for specific sales type.New customer means they have no transaction for previous two months.İ wrote something but didnt work.My query is below,

    SELECT
    {[Measures].[Fact Islem Count],[Measures].[Amount]} on columns
    ,
    EXCEPT(
    NONEMPTY
    (
    [Customer].[CustomerNumber].[CustomerNumber].ALLMEMBERS ,
    ([Measures].[Fact Islem Count],[Date].[Dt].&[2001-07-24T00:00:00] : [Date].[Dt].&[2008-08-01T00:00:00])
    )
    ,
    NONEMPTY
    (
    [Customer].[CustomerNumber].[CustomerNumber].ALLMEMBERS ,
    OpeningPeriod ([Date].[Hierarchy].[Dt], [Date].[Hierarchy].[Dt].&[2007-07-24T00:00:00].Parent.Lag(2)) :
    ClosingPeriod ([Date].[Hierarchy].[Dt], [Date].[Hierarchy].[Dt].&[2007-07-24T00:00:00].Parent.Lag(1))
    )
    )

    ON rows

    FROM [cubeSales]
    where ([Tran].[TranType].&[C])

    heykens

    July 12, 2014 at 3:57 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,302 other followers

%d bloggers like this: