MDX for retrieving the sum of first 5 months for each year

Sometimes client comes with the requirement in which they want a report which should show the SUM of first 5 months or sum of first 6 months from each year. So let’s discuss this by using MDX. You can create MDX for getting sum of first five months in the following manner. I have build the below MDX on adventureworks sample.

WITH 
  MEMBER [Measures].[Sum Of First Five Months] AS 
    Sum
    (
      {
          StrToMember
          ("[Date].[Calendar].[Month].&["
              + 
                [Date].[Calendar Year].CurrentMember.Member_Key
            + "]&[1]"
          )
        : 
          StrToMember
          ("[Date].[Calendar].[Month].&["
              + 
                [Date].[Calendar Year].CurrentMember.Member_Key
            + "]&[5]"
          )
      }
     ,[Measures].[Reseller Sales Amount]
    ) 
SELECT 
  {[Measures].[Sum Of First Five Months]} ON 0
 ,[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works];

Following is the output of above mdx query.

Value $7,221,202.69 is the SUM of "Reseller Sales Amount" for first 5 months (i.e. Jan,Feb,March,April,May) of year 2006.

You can achieve same output by using different MDX.

WITH 
  MEMBER [Measures].[Sum Of First Five Months] AS 
    Sum
    (
      {
          Descendants
          (
            [Date].[Calendar].CurrentMember
           ,[Date].[Calendar].[Month]
           ,SELF
          ).Item(0)
        : 
          Descendants
          (
            [Date].[Calendar].CurrentMember
           ,[Date].[Calendar].[Month]
           ,SELF
          ).Item(4)
      }
     ,[Measures].[Reseller Sales Amount]
    ) 
SELECT  
  {[Measures].[Sum Of First Five Months]} ON COLUMNS
 ,{
    [Date].[Calendar].[Calendar Year].&[2006]
   ,[Date].[Calendar].[Calendar Year].&[2007]
   ,[Date].[Calendar].[Calendar Year].&[2008]
  } ON ROWS

FROM [Adventure Works]; 


If you execute above MDX query then you will get the same output as first one.