How to query Date field in MDX with and without aggregate group

Question

I have a seemingly simple requirement to create a query of price data that will return four sets of price data for different dates:

1. The lowest date of the range
2. The highest date of the range
3. The aggregate sum for the entire range

The closest I have is the following query:

WITH set [Range] as {[Effective Date].[Date].&[2015-12-01T00:00:00] : [Effective Date].[Date].&[2015-12-31T00:00:00]}
select
NON EMPTY{
    filter([Effective Date].[Date].&[2015-12-01T00:00:00], [Measures].[Plan Price]),
    filter([Effective Date].[Date].&[2015-12-31T00:00:00], [Measures].[Plan Price]),
    nonempty([Measures].[Money In], [Range]),
    nonempty([Measures].[Money Out], [Range])
} on 0,
NON EMPTY{
[Region Manager].[Full Name].[Full Name]
} on 1
from [Cube]

However, the date ranges as such return the error:

Two sets specified in the function have different dimensionality.

The "filter" or "nonempty" statements will work individually but I cannot get them to work in a single query. How can I do this? Will it be helpful to use a sub query?

Answer 1

Try this:

WITH 
  SET [Range] AS 
    {
      [Effective Date].[Date].&[2015-12-01T00:00:00] 
    : [Effective Date].[Date].&[2015-12-31T00:00:00]
    }
SELECT
NON EMPTY
    {
      ([Effective Date].[Date].&[2015-12-01T00:00:00], [Measures].[Plan Price])
     ,([Effective Date].[Date].&[2015-12-31T00:00:00], [Measures].[Plan Price])
     ,{nonempty([Range]*[Measures].[Money In])}
     ,{nonempty([Range]*[Measures].[Money Out])}
    }
ON 0,
NON EMPTY [Region Manager].[Full Name].[Full Name] ON 1
FROM [Cube];

But NonEmpty is not really required so this is more concise:

WITH 
  SET [Range] AS 
    {
      [Effective Date].[Date].&[2015-12-01T00:00:00] 
    : [Effective Date].[Date].&[2015-12-31T00:00:00]
    }
SELECT
NON EMPTY
    {
      ([Effective Date].[Date].&[2015-12-01T00:00:00], [Measures].[Plan Price])
     ,([Effective Date].[Date].&[2015-12-31T00:00:00], [Measures].[Plan Price])
     ,{[Range]*[Measures].[Money In]}
     ,{[Range]*[Measures].[Money Out]}
    }
ON 0,
NON EMPTY [Region Manager].[Full Name].[Full Name] ON 1
FROM [Cube];

To aggregate you need a new custom measure:

WITH 
  SET [Range] AS 
    {
      [Effective Date].[Date].&[2015-12-01T00:00:00] 
    : [Effective Date].[Date].&[2015-12-31T00:00:00]
    }
  MEMBER [Effective Date].[Date].[All].[AggRange] AS 
    Aggregate([Range])
SELECT
NON EMPTY
    {
      ([Effective Date].[Date].&[2015-12-01T00:00:00], [Measures].[Plan Price])
     ,([Effective Date].[Date].&[2015-12-31T00:00:00], [Measures].[Plan Price])
     ,([Effective Date].[Date].[All].[AggRange],[Measures].[Money In])
     ,([Effective Date].[Date].[All].[AggRange],[Measures].[Money Out])
    }
ON 0,
NON EMPTY [Region Manager].[Full Name].[Full Name] ON 1
FROM [Cube];