# Create a measure to count the number of months in the selected time period

### Question

• Hi all,

I am here stuck in creating a measure which will be the count of number of months in the selected period.

My Date dimension(Reported Paid Date) hierarchy is:

Paid Date :: Paid Years -> Paid Quarters -> Paid Months.

The user will have the flexibility of selecting any levels from the date dimension and my measure should display the number of months in the selected period. There are a few cases to consider while creating this measure. For example:

1. If a user selects year 2010,  the measure value should be 12(assuming that all the months in the year 2012 are present in the date dimension).

2. If a user select 2010 Q3(jul, aug and sept) the value should be 3.

3. If a user select Yr 2010, Quarter 2011 Q1 and months Nov 2011 and Dec 2011, then the measure value should be 12+3+2 = 17.

In general, the measure should count the leaf level months count in the period of selection. Any selection can be made in the date dimension.

Any brilliant ideas?

Thank you in advance,

Anish

Anish Tuladhar - Do not forget to mark as answer, only if helpful.:)

Monday, May 21, 2012 2:27 PM

### Answers

• Thanks sandhya and martin ... i got the answer for solving this.... its a different approach ...

The solution for this is to create a "count measure" from the dimension table and treat it as a measure group, this would work in all scenarios in terms of calculating a count of leaf member.

Anish Tuladhar - Do not forget to mark as answer, only if helpful.:)

Wednesday, May 23, 2012 3:34 AM

### All replies

• try this sample query..

WITH member  MEASURES.MonthCount as

Descendants([Date].[Calendar].currentmember,[Adjudicated Date].[Calendar].[Month],self).count

SELECT
{MEASURES.MonthCount} on 0
from [Cubename] where  [Date].[Calendar].[Year].&[2011]

above query will give count of months for any selected level in hierarchy

Thanks and Regards, Sandhya

Monday, May 21, 2012 3:53 PM
• Hi Sandhya,

My Date dimension(Reported Paid Date) hierarchy is:

Paid Date :: Paid Years -> Paid Quarters -> Paid Months.

I am using the following MDX to count the number of paid months for any selected levels:

Member [Measures].[Number of Months] as
Descendants([Reported Paid Date].[Paid Date].currentmember,[Reported Paid Date].[Paid Date].[Paid Months],self).count

This gives correct months count when only a single year is selected, but when two years are selected and aggregated to form a member, this measure fails to give the output.

The error is: CellOrdinal 2 VALUE #Error Query (7, 13) The MDX function CURRENTMEMBER failed because the coordinate for the 'Pd Yr' attribute contains a set.

What am i missing here??

Please give your thoughts.

Thank you,

Anish

Anish Tuladhar - Do not forget to mark as answer, only if helpful.:)

Monday, May 21, 2012 5:12 PM
• Hi Anish,

it is throwing error because multi select is not allowed with Current Member

Try this..

WITH  SET Months as
[Date].[Calendar].currentmember.children
MEMBER measures.X AS
descendants([Date].[Calendar],[Date].[Calendar].[Month],self).count
SELECT Measures.X ON 0, distinct(Months) on 1
from (select   {[Date].[Calendar].[Calendar Year].&[2005]:[Date].[Calendar].[Calendar Year].&[2006]} on 0
FROM [Adventure Works])

or this...

WITH  SET Months as
descendants([Date].[Calendar].currentmember,[Date].[Calendar].[Month],self)
MEMBER measures.X AS
descendants([Date].[Calendar],[Date].[Calendar].[Month],self).count
SELECT Measures.X ON 0, distinct(Months) on 1
from (select   {[Date].[Calendar].[Calendar Year].&[2005]:[Date].[Calendar].[Calendar Year].&[2006],[Date].[Calendar].[Calendar Quarter].&[2007]&[1]} on 0
FROM [Adventure Works])

Thanks and Regards,

Sandhya

Monday, May 21, 2012 7:05 PM
• Sandhya is correct. You cannot use CurrentMember when multiselects are present in your query. If your query tool uses the WHERE clause to filter members, you can use the EXISTING declaration on the set of months.

```WITH MEMBER [Measures].[# of Months] AS
COUNT( EXISTING [Date].[Calendar].[Month].Members )
SELECT	{
[Measures].[# of Months]
} ON COLUMNS
FROM	[Adventure Works]
WHERE	({
[Date].[Calendar].[Calendar Year].&[2007],
[Date].[Calendar].[Calendar Semester].&[2008]&[1],
[Date].[Calendar].[Month].&[2008]&[8]
})```

If your query tool is using subselects, it won't that easy. You may want to look at some of Mosha's older blog post also.

http://www.mosha.com/msolap/articles/mdxmultiselectcalcs.htm

HTH, Martin

http://martinmason.wordpress.com

Tuesday, May 22, 2012 1:50 AM
• Thanks sandhya and martin ... i got the answer for solving this.... its a different approach ...

The solution for this is to create a "count measure" from the dimension table and treat it as a measure group, this would work in all scenarios in terms of calculating a count of leaf member.

Anish Tuladhar - Do not forget to mark as answer, only if helpful.:)

Wednesday, May 23, 2012 3:34 AM