title: MDX: Aggregate [Date].[Calendar].[Quarter] level to create new [Date].[Calendar].[Semi Annual] Level
body: <p>I have a Date Dimension that has Month Hierarchy</p>
  
  <pre><code>Year -&gt; Quarter -&gt; Month -&gt; Day -&gt; Hour
  </code></pre>
  
  <p>I have two measures. Measure_A (Aggregation is Max), Measure_B (Aggregation is SUM)</p>
  
  <p>I need to Multiply Measure_A by 24 on "Day" level then I need to SUM it Semi Annually.</p>
  
  <p>Is it possible to create a new hierarchy by just using an MDX query? Below is the output that i am trying to achieve</p>
  
  <pre><code>Year | CY H1 | Measure_C | Measure_B
  </code></pre>
  
  <p>Where: <code>Measure_C</code> is the SUM of (Measure_A * 24) from January 1 to June 30, and <code>CY H1</code> will be the new level.</p>
  
  <p>I don't have any problem with Measure_B because its aggregation is SUM. Only with Measure_A beacause the aggregation is Max.</p>
  
  <p>Is this possible to achieve without altering the cube, just do an MDX? Thanks.</p>
  
  <pre><code>Updated: 
  </code></pre>
  
  <p>This is my query and output as of the moment, </p>
  
  <p>Query:</p>
  
  <pre><code>With
    MEMBER [Measures].[Measure_C] AS
      SUM (   Descendants ( [Date].[Date Calendar],  [Date].[Date Calendar].[Day]), [Measures].[Measure_A] * 24 )
      , Format_String = "#,##0"
  
  Select 
    {
      [Measures].[Measure_C]
    } ON Columns,
    NON Empty
    {
      [Date].[Date Calendar].[Quarter].ALLMEMBERS
    } ON Rows
  From [Cube]
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>+------+---------+-----------+
  | Year | Quarter | Measure_C |
  +------+---------+-----------+
  | 2011 | Q1      |        12 |
  | 2011 | Q2      |        45 |
  | 2011 | Q3      |        12 |
  | 2011 | Q4      |        25 |
  +------+---------+-----------+
  </code></pre>
  
  <p>What I am trying to do is to merge Q1 and Q2 to get Calendar Semester for the first half (CY H1)</p>
  
  <p>Required output:</p>
  
  <pre><code>+------+---------+-----------+
  | Year | Half Yr | Measure_C |
  +------+---------+-----------+
  | 2011 | CY H1   |        57 |
  | 2011 | CY H2   |        37 |
  +------+---------+-----------+
  </code></pre>
  
  <p>Please note that we don't have a natural Hierarchy for <code>CY H1</code>. But according to the comment below, this is not possible using mdx alone.</p>
  
  <p>By the way, I am doing this for reporting services so I guess I will just do the magic in the presentation layer since this is not achievable using MDX query alone. :)</p>