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

Answer 1

this should work:

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

hth,
gerhard

---

- www.pmOne.com -

Answer 2

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

Answer 3

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