locked
Compute sessions conducted from distinct clients in every financial year RRS feed

  • Question

  • Hi,

    I have computed distinct session in every fiscal year using this Calculated Field formula

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

    I have also computed new clients this fiscal year by using this Calculated Field formula

    =[Distinct Clients till this Fiscal Year]-[Distinct Clients till Previous Fiscal Year]

    Distinct clients till this Fiscal Year have been computed by using this Calculated Field formula

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

    Distinct clients till Previous Fiscal Year have been computed by using this Calculated Field formula

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

    So for FY 2007, 2008, 2009, 2010, 2011 and 2012, the result of Fiscal Year Distinct Sessions is 1, 31, 65, 90, 90 and 71.  The result of New clients this Fiscal Year is 1, 12, 21, 22, 21 and 9.

    All good so far.

    Now I wish to compute the sessions from new clients for all fiscal years. In other words, I want to know how many of the 65 sessions conducted in FY 2009 were from the 21 new clients?

    What formula would I have to frame for this?


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

    Friday, February 22, 2013 2:20 PM

Answers

All replies