Compute maximum sessions conducted in any month of a financial year

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

• Sunday, March 03, 2013 10:03 AM

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"?

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

### All Replies

• Monday, March 04, 2013 9:45 AM

this should work:

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

hth,
gerhard

- www.pmOne.com -

• Tuesday, March 05, 2013 10:33 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:45 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