# 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 2, 2013 7:43 AM

• Hi,

Yes, I could solve the problem.  Thank you for following up.

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

Tuesday, November 19, 2013 8:27 AM

### All replies

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)

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

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)