Answered by:
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
- Edited by Ashish Mathur Tuesday, March 5, 2013 10:39 AM
Saturday, March 2, 2013 7:43 AM
Answers
-
Hi,
Yes, I could solve the problem. Thank you for following up.
Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com
- Proposed as answer by Ed Price - MSFTMicrosoft employee Sunday, November 24, 2013 1:10 AM
- Marked as answer by Ed Price - MSFTMicrosoft employee Monday, December 16, 2013 7:30 PM
Tuesday, November 19, 2013 8:27 AM
All replies
-
Any ideas please.
Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com
Tuesday, March 5, 2013 10:42 AM -
Ashish,
Have you made any progress with this?
Thanks!
Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)
Answer an interesting question? Create a wiki article about it!Tuesday, November 19, 2013 8:24 AM -
Hi,
Yes, I could solve the problem. Thank you for following up.
Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com
- Proposed as answer by Ed Price - MSFTMicrosoft employee Sunday, November 24, 2013 1:10 AM
- Marked as answer by Ed Price - MSFTMicrosoft employee Monday, December 16, 2013 7:30 PM
Tuesday, November 19, 2013 8:27 AM -
Great! Do you know what the problem was?
Thanks!
Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)
Answer an interesting question? Create a wiki article about it!Sunday, November 24, 2013 1:11 AM -
Hi,
I do not really remember now. I solved it quite some time back. Thank you for following up.
Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com
Sunday, November 24, 2013 2:33 AM