none
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

    Tuesday, February 19, 2013 1:50 AM

Answers

  • 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





    Tuesday, February 19, 2013 2:32 AM
    Answerer

All replies

  • 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





    Tuesday, February 19, 2013 2:32 AM
    Answerer
  • Hi,

    Thank you.  Those links helped.


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

    Tuesday, February 19, 2013 1:32 PM