locked
Compute distinct sessions conducted from new clients only RRS feed

  • Question

  • Hi,

    I dragged years to the column area of a Pivot Table and created the following calculated field formula (Fiscal Year Distinct sessions) to compute the distinct sessions conducted in each financial year (ended March 31)

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

    Distinct sessions have in turn been computed by using the following calculated field formula

    =DISTINCTCOUNT(Feedback[Date])

    So far all results of the first calculated field formula are correct.

    Now I wish to compute the Fiscal Year distinct sessions from new clients. A new client is a client from whom I have not derived business in any of the previous financial years.

    For determining this figure, I tried writing the following calculated field formula

    =CALCULATE([distinct sessions],FILTER(Feedback,[Distinct Clients till this Fiscal Year]-[Distinct Clients till Previous Fiscal Year]>=0)
                            ,DATESBETWEEN(Calendar1[DateKey]
                                                            ,STARTOFYEAR(LASTDATE(Calendar1[DateKey]),"3-31")
                                                            ,endOFYEAR(LASTDATE(Calendar1[DateKey]),"3-31")
                                                           )
                           )

    The result i get is incorrect (there is no error in the formula as such).

    I computed Distinct clients till this Fiscal year with the help of the following calculated field formula

    =if([Fiscal Year Distinct Clients]=0,BLANK(),CALCULATE([Distinct Clients]
                            ,DATESBETWEEN(Calendar1[DateKey]
                                                            ,BLANK()
                                                            ,endOFYEAR(LASTDATE(Calendar1[DateKey]),"3-31")
                                                           )
                           ))

    I computed Distinct clients till previous Fiscal year with the help of the following calculated field formula

    =if([Fiscal Year Distinct Clients]=0,BLANK(),CALCULATE([Distinct Clients]
                            ,DATESBETWEEN(Calendar1[DateKey]
                                                            ,BLANK()
                                                            ,startOFYEAR(LASTDATE(Calendar1[DateKey]),"3-31")
                                                           )
                           ))

    To compute new clients this fiscal year, I wrote a calculated field formula as [Distinct clients till this Fiscal year]-[Distinct clients till previous Fiscal year].  This result is absolutely correct which means that these two calculated field formulas are correct.

    Could you help me to correct the formula for computing Fiscal Year distinct sessions from new clients.

    Thank you.


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


    Wednesday, February 27, 2013 2:05 AM

Answers

  • Wow ... nice MVP question.

    I made a few tests with the following data.

    Customer
    A
    B
    C

    The 'Calendar' table (note the totally arbitrary FiscalYear column is):

    Date FiscalYear
    01/01/2013 2011
    01/02/2013 2011
    01/03/2013 2012
    01/04/2013 2012
    01/05/2013 2013
    01/06/2013 2013

    The 'Data' table.

    Date Customer
    01/01/2013 A
    01/02/2013 A
    01/03/2013 A
    01/04/2013 A
    01/06/2013 A
    01/03/2013 B
    01/04/2013 B
    01/05/2013 B
    01/06/2013 B
    01/05/2013 C
    01/06/2013 C

    My approach is to first determine the relevant filters, then calculate whatever measure I want for these values (using a CALCULATE expression). Here I simply used a measure defined as COUNTROWS("Data'), to test the filter expression. Here are the results:

    A 5 2
    2011 2 2
    01/01/2013 1 1
    01/02/2013 1 1
    2012 2
    01/03/2013 1
    01/04/2013 1
    2013 1
    01/06/2013 1
    B 4 2
    2012 2 2
    01/03/2013 1 1
    01/04/2013 1 1
    2013 2
    01/05/2013 1
    01/06/2013 1
    C 2 2
    2013 2 2
    01/05/2013 1 1
    01/06/2013 1 1
    Total   11 6

    I also included a daily level, so as to check that being a new customer is true for the whole fiscal year.

    Here is the expression:

    [Count data for new customers] := CALCULATE( 
    [Your Measure Here]
    , KEEPFILTERS(
      GENERATE( VALUES(Customers)
        , TOPN( 1,
           CALCULATETABLE(
            SUMMARIZE( Data, Calendar[FiscalYear])
             , ALLEXCEPT(Data, Customers)
           )
           , Calendar[FiscalYear], 1
          )   
      )
     )
     )

    For each customer in the current selection, calculate the first fiscal year for the first row of data. The expression is then wrapped into a KEEPFILTERS expression, so as to add the values found to FiscalYear to the current filter context.

    Let me know if it works for you.


    The Data Specialist (Blog)

    • Marked as answer by Ashish Mathur Thursday, February 28, 2013 12:11 PM
    Wednesday, February 27, 2013 1:43 PM
  • Hi,

    your YearKey column represents the calendar year. Hence, the formula wil be generated for calendar years.

    If you want to calculate your fiscal year, then you will have to take the offset into account. Assuming that the months April to December, are the ones where the calendar year and the fiscal year match, you could use a formula like:
    = YEAR( [DateKey]) - IF(  MONTH( [DateKey] ) >= 4, 0, 1) 


    The Data Specialist (Blog)

    • Marked as answer by Ashish Mathur Thursday, February 28, 2013 12:11 PM
    Thursday, February 28, 2013 9:39 AM
  • ok, check this out:

    http://sdrv.ms/14pPZfT

    should solve your problem

    btw, there was a typo in your test-data, the first row of table "billing data" should be on 23.12.2012

    hope that helps,
    gerhard


    - www.pmOne.com -

    • Marked as answer by Ashish Mathur Wednesday, June 26, 2013 1:15 PM
    Tuesday, June 25, 2013 7:31 PM
    Answerer

All replies

  • Wow ... nice MVP question.

    I made a few tests with the following data.

    Customer
    A
    B
    C

    The 'Calendar' table (note the totally arbitrary FiscalYear column is):

    Date FiscalYear
    01/01/2013 2011
    01/02/2013 2011
    01/03/2013 2012
    01/04/2013 2012
    01/05/2013 2013
    01/06/2013 2013

    The 'Data' table.

    Date Customer
    01/01/2013 A
    01/02/2013 A
    01/03/2013 A
    01/04/2013 A
    01/06/2013 A
    01/03/2013 B
    01/04/2013 B
    01/05/2013 B
    01/06/2013 B
    01/05/2013 C
    01/06/2013 C

    My approach is to first determine the relevant filters, then calculate whatever measure I want for these values (using a CALCULATE expression). Here I simply used a measure defined as COUNTROWS("Data'), to test the filter expression. Here are the results:

    A 5 2
    2011 2 2
    01/01/2013 1 1
    01/02/2013 1 1
    2012 2
    01/03/2013 1
    01/04/2013 1
    2013 1
    01/06/2013 1
    B 4 2
    2012 2 2
    01/03/2013 1 1
    01/04/2013 1 1
    2013 2
    01/05/2013 1
    01/06/2013 1
    C 2 2
    2013 2 2
    01/05/2013 1 1
    01/06/2013 1 1
    Total   11 6

    I also included a daily level, so as to check that being a new customer is true for the whole fiscal year.

    Here is the expression:

    [Count data for new customers] := CALCULATE( 
    [Your Measure Here]
    , KEEPFILTERS(
      GENERATE( VALUES(Customers)
        , TOPN( 1,
           CALCULATETABLE(
            SUMMARIZE( Data, Calendar[FiscalYear])
             , ALLEXCEPT(Data, Customers)
           )
           , Calendar[FiscalYear], 1
          )   
      )
     )
     )

    For each customer in the current selection, calculate the first fiscal year for the first row of data. The expression is then wrapped into a KEEPFILTERS expression, so as to add the values found to FiscalYear to the current filter context.

    Let me know if it works for you.


    The Data Specialist (Blog)

    • Marked as answer by Ashish Mathur Thursday, February 28, 2013 12:11 PM
    Wednesday, February 27, 2013 1:43 PM
  • Hi,

    Thank you for taking the time out to help me.  The calculated Field formula below is generating the Sessions from new client in the calendar year (not Fiscal year) 

    =CALCULATE( 
    [distinct sessions]
    , KEEPFILTERS(
      GENERATE( VALUES(feedback)
        , TOPN( 1, 
           CALCULATETABLE( 
            SUMMARIZE(feedback,Calendar1[Yearkey]) 
             , ALLEXCEPT(feedback,Feedback[Organised by]) 
           )
           ,Calendar1[yearkey],1
          )    
      )
     )
     )

    Distinct sessions have been calculated as follows:

    =DISTINCTCOUNT(Feedback[Date])

    Feedback is the table name which has Date and customer names (same as your Data Table). YearKey is the year column in the Calendar Table. As mentioned earlier, the result I get tally for the calendar year but not the fiscal year i.e. 1 April to 31 March.

    I feel the problem is with my YearKey column.  Currently this is computed as a calculated column formula

    =year(Calendar1[DateKey])

    Could you help me identify the error.

    Thank you.


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

    Thursday, February 28, 2013 2:38 AM
  • Hi,

    your YearKey column represents the calendar year. Hence, the formula wil be generated for calendar years.

    If you want to calculate your fiscal year, then you will have to take the offset into account. Assuming that the months April to December, are the ones where the calendar year and the fiscal year match, you could use a formula like:
    = YEAR( [DateKey]) - IF(  MONTH( [DateKey] ) >= 4, 0, 1) 


    The Data Specialist (Blog)

    • Marked as answer by Ashish Mathur Thursday, February 28, 2013 12:11 PM
    Thursday, February 28, 2013 9:39 AM
  • Hi,

    That does not work either.  I modified the formula to

    =CALCULATE( 
    [distinct sessions]
    , KEEPFILTERS(
      GENERATE( VALUES(feedback)
        , TOPN( 1, 
           CALCULATETABLE( 
            SUMMARIZE(feedback,Calendar1[fiscal year]) 
             , ALLEXCEPT(feedback,Feedback[Organised by]) 
           )
           ,Calendar1[fiscal year],1
          )    
      )
     )
     )

    In the calendar1 worksheet, I inserted a column title as Fiscal Year. The formula in this column was

    =IF(MONTH([@DateKey])<=3,YEAR([@DateKey])-1,YEAR([@DateKey]))

    The result is wrong (though different from the one that I got when using YearKey.

    Any ideas?


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

    Thursday, February 28, 2013 10:02 AM
  • Hard to say, without knowing what is wrong.

    Can you provide a small sample of your data, a case where the calculation fails, and what the exepcted result should be?


    The Data Specialist (Blog)

    Thursday, February 28, 2013 10:31 AM
  • Hi,

    Please accept my humble apologies - your formula works fine.

    Thank you.


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

    Thursday, February 28, 2013 12:12 PM
  • Hi,

    I have a Pivot Table where I dragged Fiscal years to the column labels.  I also have the following calculated Field formula to compute the Sessions conducted at new clients

    =CALCULATE( 
    [Sessions conducted]
    , KEEPFILTERS(
      GENERATE( VALUES(feedback)
        , TOPN( 1, 
           CALCULATETABLE( 
            SUMMARIZE(feedback,Calendar1[fiscal year]) 
             , ALLEXCEPT(feedback,Feedback[Organised by]) 
          )
           ,Calendar1[fiscal year],1
          )    
     )
     )
     )

    The result of this formula is correct. It counts the sessions conducted at new clients contacted in every financial year.

    However, if I drag Session Type to the slicer and select two out of three options, the result is incorrect.

    So the question is "What modification should be made in the formula above to take into account options selected in the Slicer"

    Please help.


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

    Tuesday, March 26, 2013 9:44 AM
  • What does "incorrect" mean exactly? What are your expected results?


    The Data Specialist (Blog)

    Tuesday, March 26, 2013 11:18 AM
  • Hi,

    Sorry for the confusion - here is the file with the Problem description - http://sdrv.ms/14mVe3s

    Thank you for your time.


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

    Wednesday, March 27, 2013 1:06 AM
  • Hi Ashish,

    that makes it very clear.

    Since there was an MS Excel session in 2010 for Make My Trip, they can no longer be considered new customers.

    If you want the definition of a new customer to be dependant on Type of session conducted, you can modify the ALLEXCEPT part.

    ALLexcept(Feedback;Feedback[Organised by]; Feedback[Type of session])

    Cheers


    The Data Specialist (Blog)

    Wednesday, March 27, 2013 9:05 AM
  • Hi,

    Thank you.  That helped.


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

    Wednesday, March 27, 2013 2:34 PM
  • Hi,

    Thank you for your help so far.  I would now like to determine the new clients visited in each quarter of all financial years.

    You may access the file from here - http://sdrv.ms/14pygbS

    The question is clearly stated in the file.


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

    Thursday, March 28, 2013 2:28 AM
  • From what I could see pulling the Fiscal Year Quarter field in the pivot table would deliver the expected results.

    Note I recommend you place the Fiscal Year Quarter column  in your Calendar table, and not in the Feedback table.


    The Data Specialist (Blog)

    Thursday, March 28, 2013 12:36 PM
  • Hi,

    I do not want to pull quarters in the Pivot Table.  I would like the Pivot Table to have fiscal Years (as already shown in the file link shared above).  I just want four more rows to be added there - New clients contacted in Q1, New clients contacted in Q2, New clients contacted in Q3 and New clients contacted in Q4.


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

    Friday, March 29, 2013 10:55 AM
  • OK, I did not understand you wanted 4 additional measures.

    Adding a filter on the Fiscal Year Quarter in the CALCULATE statement should do the job:

    [New Clients Visited in Q1]
    =CALCULATE(
    [Clients Visited]
    , KEEPFILTERS( ... )   
    , Calendar1[Fiscal Year Quarter] = "Q1"
    )

    This assumes you attach the Fiscal Year Quarter to the Calendar table.


    The Data Specialist (Blog)

    Friday, March 29, 2013 11:12 AM
  • Hi,

    That does not work.  I implemented your solution in the file shared at the link above but I get incorrect results.

    Please help.

    Thank you.


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

    Friday, March 29, 2013 11:55 AM
  • Which part does not work? The results match the description for the selection "VBA" + "VBA and MS Excel".


    The Data Specialist (Blog)

    Friday, March 29, 2013 6:39 PM
  • Hi,

    When you clear all filters from the slicer.


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

    Friday, March 29, 2013 11:18 PM
  • Can you be more specific? If I put Fiscal Year and Organized By on rows and the measures on columns, everything looks fine.

    The Data Specialist (Blog)

    Saturday, March 30, 2013 1:04 PM
  • Hi,

    Sorry for the confusion.  Please try this

    1. Refer to the file at this link - http://sdrv.ms/14pygbS
    2. Ensure that there are no slicer selections

    For year 2012, the new clients visited are 9 (cell G6 of Question sheet).  The quarter wise figures below i.e. those in range G7:G10 do not add up to 9.  This is the problem for the other years as well.

    Hope this is clear now.


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

    Saturday, March 30, 2013 11:00 PM
  • Hi,

    Could you kindly help


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

    Tuesday, April 2, 2013 9:48 AM
  • Hi,

    a customer is considered a new customer for the whole first new year. If you visit a same (new) customer on Q1 and Q4, then you will count this customer once for Q1 and once for Q4, but also once only for the whole year. This is indeed not an additive behaviour, which I expected given my understanding of your requirements.

    Do you want to handle this case differently? Then how?


    The Data Specialist (Blog)

    Tuesday, April 2, 2013 10:45 PM
  • Hi,

    Thank you for replying.  I worked around the problem by taking a difference between the Clients visited till the end of the every quarter and Clients visited till the previous quarter.

    Thank you for your help.


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

    Wednesday, April 3, 2013 3:52 AM
  • Hi,

    You had helped me with computing the "Sessions conducted at new clients" financial year wise by sharing the following calculated Field formula.

    CALCULATE( 
    [Sessions conducted]
    , KEEPFILTERS(
      GENERATE( VALUES(feedback)
        , TOPN( 1, 
           CALCULATETABLE( 
            SUMMARIZE(feedback,Calendar1[fiscal year]) 
             , ALLEXCEPT(feedback,Feedback[Organised by]) 
          )
           ,Calendar1[fiscal year],1
          )    
     )
     )
     )

    This worked absolutely fine. My requirement now is to compute the revenue earned from these new clients. In the PowerPivot tab (Billing Data), I have a column titled as Total invoice value.  I have set up a relationship between the date column of the Billing tab and Date column of Feedback tab.

    Could you kindly help me with the calculated field formula for computing the "Revenue earned from new clients".

    Thank you.


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

    Saturday, June 22, 2013 5:20 AM
  • Hi Ashish Mathur,

    You try to use below measure :

    CALCULATE(
          CALCULATE(SUM(Invoice[Total invoice value]),Feedback),
          KEEPFILTERS(
                TOPN(1,
                     CALCULATETABLE(
                             SUMMARIZE(Feedback,Calendar1[Fiscal Year]),
                             ALLEXCEPT(Feedback,Feedback[Organised by])
                    ),
                    Calendar1[Fiscal Year],1
                )
          )
     )

    Regards,

    Saturday, June 22, 2013 1:42 PM
  • Hi,

    I tried the following formula and it did not work

    CALCULATE(
          [total adjusted revenue],
          KEEPFILTERS(
                TOPN(1,
                     CALCULATETABLE(
                             SUMMARIZE(Feedback,Calendar1[Fiscal Year]),
                             ALLEXCEPT(Feedback,Feedback[Organised by])
                    ),
                    Calendar1[Fiscal Year],1
                )
          )
     )

    Total adjusted revenue is a calculated field formula

    =[Total invoiced value]-[Total reimbursements]-[Total service tax]

    This is showing revenue for the first financial year only (financial years appear in column labels).

    Please help.


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


    Sunday, June 23, 2013 4:35 AM
  • Hi.. Ashish Mathur,

    I also had your share sample date before. I also tested it with your data sample and it worked. As your suggestion, You make relationship the date of feedback table with the date of billing table. The date of feedback table is multi values so that, you want to calculate sum of values in billing table. You need to wrap the calculation into like... CALCULATE(-------,Feedback). if [total adjusted revenue] measure calculate on billing table.you can try below :

    CALCULATE([total adjusted revenue],Feedback)

    Rgds,

    Sunday, June 23, 2013 6:55 AM
  • Hi,

    I am sorry for the incorrect statement made earlier.  There is no relationship between Data column on Billing Table and Date column on Feedback table.  Furthermore, the dates in the two columns are not the same either because if a session happened on June 12-13 2012, then the billing can be on June 15, 2012.

    There is a relationship between the data column of the billing table with the date column on the calendar1 table (which has running dates from 2008 to 2019).  Also, there is a relationship between the date column on the calendar1 table with the date column on the Feedback table.

    Any ideas?


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

    Monday, June 24, 2013 2:52 AM
  • could you share the sample workbook again?

    it seems like the link is broken


    - www.pmOne.com -

    Monday, June 24, 2013 12:52 PM
    Answerer
  • Hi,

    Thank you for replying.  In the file link below, you may see the data and question.

    http://sdrv.ms/10hkhlE


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

    Tuesday, June 25, 2013 2:55 AM
  • I will take a look at the workbook this evening

    in Terms of calculating new customers (or in your case new Clients) you may also want to take a look here:
    https://gbrueckl.wordpress.com/2013/02/22/another-post-about-calculating-new-and-returning-customers/

    it may be helpful for your Scenario 


    - www.pmOne.com -

    Tuesday, June 25, 2013 7:25 AM
    Answerer
  • Hi,

    I have already computed new clients by using the following calculated field formula

    =CALCULATE( 
    [Clients visited]
    , KEEPFILTERS(
      GENERATE( VALUES(feedback)
        , TOPN( 1, 
           CALCULATETABLE( 
            SUMMARIZE(feedback,Calendar1[fiscal year]) 
             , ALLEXCEPT(feedback,Feedback[Organised by]) 
          )
           ,Calendar1[fiscal year],1
          )    
     )
     )
     )

    This gives me the year wise new clients visited.

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

    Tuesday, June 25, 2013 7:28 AM
  • ok, check this out:

    http://sdrv.ms/14pPZfT

    should solve your problem

    btw, there was a typo in your test-data, the first row of table "billing data" should be on 23.12.2012

    hope that helps,
    gerhard


    - www.pmOne.com -

    • Marked as answer by Ashish Mathur Wednesday, June 26, 2013 1:15 PM
    Tuesday, June 25, 2013 7:31 PM
    Answerer
  • Hi,

    A server error message pops up when I click on the link - will try to download the workbook later.

    Thank you.


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

    Wednesday, June 26, 2013 2:50 AM
  • Hi,

    Thank you for your answer.  I could download the file and the result of 3 and 79 is as expected.  Could you kindly verify my understanding for the "ClientsFiscalyearNumber" calculated column

    Explanation

    The formula is applying a filer on each Customer Name.  So when it processes the first row, it will filter on all rows where Religare Enterprises is found (so it will filter and return 3 rows) and in the 3 rows it will rank the [Fiscal Year of Invoice] in the current row to the 3 [Fiscal Year of Invoice] returned by the Filter.

    Then when it goes to the next row, it will filter on all rows where Indus Towers is found (so it will filter and return 2 rows) and in the 2 rows it will rank the [Fiscal Year of Invoice] of the current row to the 2 [Fiscal Year of Invoice] returned by the Filter.

    Is my understanding above correct?

    Also, what is the role of the EARLIER() function here?


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

    Wednesday, June 26, 2013 1:15 PM
  • ClientsFiscalYearNumber actually ranks the different FiscalYears on which we made Business with a client

    i used RANKX() on the whole table ( ALL(TblBillingData) ) and used FILTER in combination with EARLIER() to get all records associated with the current Client (row-context)

    if he was a Client in 2008, 2010, 2011, and 2012 then all records from 2008 would be assigned "1", 2010 would be "2", 2011 would be "3" etc.

    the rest is quite simple, if ClientsFiscalYearNumber is 1, means that it was the first fiscal year we made Business with that customer making the associated rows relevant for your "Is new Client"-calculation

    you can find more Details on this Approach on my blog where i posted the link in my previous reply

    hth,
    gerhard


    - www.pmOne.com -

    Wednesday, June 26, 2013 4:49 PM
    Answerer
  • Thank you for that explanation.

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

    Thursday, June 27, 2013 2:09 AM