Compute Unique clients visited in every financial Year

Question

Hi,

Via a Power Pivot, I have created a Pivot Table with years in ranging from 2007 to 2013 in F4:K4.  In F5:K5, I have computed the Distinct clients visited in the Fiscal Year (ended 31 March) by using the following Calculated Field formula

=CALCULATE([Distinct clients]
                        ,DATESBETWEEN(Calendar[DateKey]
                                                        ,STARTOFYEAR(LASTDATE(Calendar[DateKey]),"3-31")
                                                        ,endOFYEAR(LASTDATE(Calendar[DateKey]),"3-31")
                                                       )
                       )

I computed Distinct clients by using the following calculated Field formula

=DISTINCTCOUNT([Organised by])

All is good so far.  What I additionally want "New clients visited in the Fiscal Year (ended 31 March)"

So if we are looking at Fiscal Year ended 2012 i.e. 1 April 2011 to 31 March 2012, then I only want to count those Distinct clients in the period 1 April 2011 to 31 March 2012 which were not there from 1 April 2006 to 31 March 2011.  Likewise if we are looking at Fiscal Year ended 2013 i.e. 1 April 2012 to 31 March 2013, then I only want to count those Distinct clients in the period 1 April 2012 to 31 March 2013 which were not there from 1 April 2006 to 31 March 2012.

I am guessing that this problem will have to be solved in two parts:

1. Part 1 - For a particular Fiscal year (say 1 April 2011 to 31 March 2012) count how many times has a client appeared from 1 April 2006 to 31 March 2011.  If that count is 0, then it means that it is a new client and therefore assign a value of 1, else 0

2. Part 2 - Sum all the 1 values.

Could you kindly help me to frame the Calculated Field formula for this.

Thank you.

---

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

Answer 1

Hi Ashish -

Check out this post on PowerPivotPro.com from a few weeks back.  Pretty much the same challenge addressed.  David Hager calc'd new clients per day with a pattern that's essentially count of distinct clients inception to current date minus count of distinct clients inception to previous period.  And there was a follow up post by Rob Collie for Monthly here.  Copying his code here for reference:

NewCustomersPerDay:
=CALCULATE([DistinctCustomersPerDay],DATESBETWEEN(Table1[Date], BLANK(),LASTDATE(Table1[Date])), All(Table1[Date])) - 
CALCULATE([DistinctCustomersPerDay],DATESBETWEEN(Table1[Date], BLANK(),LASTDATE(Table1[Date])-1), All(Table1[Date]))

Also, it would probably make your Fiscal year calcs much easier if you add a column to your Date table with the Fiscal year.  That way you can drop Fiscal Year on rows and LASTDATE will naturally be 3/31 of that year.  Could just add a calc column with the following:

=IF(MONTH([Date])>3,YEAR([Date])+1,YEAR([Date]))

Hope that helps.

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 2

Hi,

Thank you.  Those links helped.

---

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