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
