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

Answer 1

Any ideas please.

---

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