MDX: Retrieving measure from specific month?

Question

• Hi!

I have a cube with a Calculated Member with the following expression:

This works fine for the year specified in the expression. But I want the expression to be flexible and to work for all years. So what I want is to be able to sum all PROG_V measures from the month of April for each year.

The Calculated Member will be used in a SSRS-report to display prognosis values.

Regards David

D

Friday, February 21, 2014 9:18 AM

• Hi,

To avoid to have to list all the april months , a "month of year"  attribute should be defined in your date dimension .

Your calculated member could look like:

SUM([DATUM D]..[Month of Year].&[4],[Measures].[PROG V])

```WITH
MEMBER [Measures].[Total april all years] AS
sum([Date].[Month of Year].&[4],
[Measures].[Internet Sales Amount])
SELECT {[Measures].[Total april all years],
[Measures].[Internet Sales Amount]} ON 0,
[Product].[Category].[Category] ON 1

Philip

• Marked as answer by Friday, February 21, 2014 10:47 AM
Friday, February 21, 2014 9:59 AM

All replies

• Hi,

To avoid to have to list all the april months , a "month of year"  attribute should be defined in your date dimension .

Your calculated member could look like:

SUM([DATUM D]..[Month of Year].&[4],[Measures].[PROG V])

```WITH
MEMBER [Measures].[Total april all years] AS
sum([Date].[Month of Year].&[4],
[Measures].[Internet Sales Amount])
SELECT {[Measures].[Total april all years],
[Measures].[Internet Sales Amount]} ON 0,
[Product].[Category].[Category] ON 1