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

    Question

  • 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

    Sunday, March 03, 2013 10:03 AM

Answers

  • this should work:

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

    hth,
    gerhard


    - www.pmOne.com -

    • Marked as answer by Ashish Mathur Tuesday, March 05, 2013 10:34 AM
    Monday, March 04, 2013 9:45 AM

All replies

  • this should work:

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

    hth,
    gerhard


    - www.pmOne.com -

    • Marked as answer by Ashish Mathur Tuesday, March 05, 2013 10:34 AM
    Monday, March 04, 2013 9:45 AM
  • 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:33 AM
  • 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

    Tuesday, March 05, 2013 10:45 AM