none
Clients transacted with for every year since the past x years

    Question

  • Hi,

    I have a table named feedback which has two columns - Date and Organised By.  The organized by column lists down the clients which I visited on a specific date.

    I would like to determine the following in a Power Pivot:

    "How many of my repeat customers were also with me x year ago".  X is a year which I want to be able to select from a slicer.  So for example, if I know that the clients visited in Financial Year 2012 i.e. April 1, 2012 to March 31, 2013 are 24 (Calculated field formula shown below) and if I also know that the new clients visited in the same period are 9, then I want to know that of the 15 clients left, how many have been with me since April 1, 2010 i.e. Financial Year 2010.  The Year 2010 should be selectable via a slicer.  My purpose is to determine loyal customers.

    This is the information which I already have.

    Clients visited is a calculated field which has been computed as follows:

    =CALCULATE(DISTINCTCOUNT([Organised by]))

    Put simply, I simply want to know "How many clients which I visited this year, I had visited x years back as well".  X is a number (say 2) which I will make in a slicer.

    Could you kindly help me with framing a calculated field and/or calculated column formula for solving this problem.

    Thank you.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com




    Saturday, March 02, 2013 7:43 AM

Answers

All replies