Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Compute maximum sessions conducted in any month of a financial year

Respondida Compute maximum sessions conducted in any month of a financial year

  • Sunday, March 03, 2013 10:03 AM
     
      Has Code
    Sign In to Vote
    0

    Hi,

    I have a table named feedback with a column of dates on which sessions were conducted.  If there were 20 participants in a particular sessions, then the date appears 20 times.  This data is from August 2006 till date.

    I wish to compute the maximum number of sessions conducted in any one month of the financial year.  So for every calendar month, I wish to compute the sessions conducted and then take the maximum of those sessions.

    If it is of any help, I also have a table named Calendar1 with a Date key column.  This has consecutive days from 1/1/2006 to 31/12/2019.

    Currently, in the Pivot Table I have dragged financial years (financial year ended March 31) to the column labels.  I compute Sessions conducted by using the following calculated field formula

    =DISTINCTCOUNT(Feedback[Date])

    This gives me the correct count of sessions conducted in every financial year (April 1 to March 31) for all years from 2007 (April 1, 2007 to March 31, 2008) to 2012 (April 1, 2012 to March 31, 2013).

    What kind of a formula will I have to write to compute the "maximum number of sessions conducted in any one month of the financial year"?

    Please help.

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

     

All Replies

  • Monday, March 04, 2013 9:45 AM
     
     Answered
    Sign In to Vote
    0

    this should work:

    MyMeasure:=MAXX(VALUES('Calendar1'[Month]), DISTINCTCOUNT(Feedback[Date]))

    hth,
    gerhard

    - www.pmOne.com -

     
  • Tuesday, March 05, 2013 10:33 AM
     
      Has Code
    Sign In to Vote
    0

    Hi,

    Thank you.  That helped.  I used the following calculated field measure

    =MAXX(VALUES(Calendar1[MonthKey]),[Sessions conducted])

    Sessions conducted in turn was computed via the following calculated field measure

    =DISTINCTCOUNT(Feedback[Date])

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

     
  • Tuesday, March 05, 2013 10:45 AM
     
     
    Sign In to Vote
    0

    Hi,

    Taking it one level further, how can I compute the "The number of months in which my sessions exceeded the average number of sessions conducted in the FY"

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