Compute Unique clients visited in every financial Year

Compute Unique clients visited in every financial Year

• Tuesday, February 19, 2013 1:50 AM

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

All Replies

• Tuesday, February 19, 2013 2:32 AM

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
http://brentgreenwood.blogspot.com

• Tuesday, February 19, 2013 1:32 PM

Hi,