none
MDX: Retrieving measure from specific month?

    Question

  • Hi!

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

    SUM([DATUM D].[MÅNADSNAMN].&[2013]&[APRIL],[Measures].[PROG V])

    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

Answers

  • 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
    FROM [Adventure Works]

    Philip

    • Marked as answer by TexasTour 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
    FROM [Adventure Works]

    Philip

    • Marked as answer by TexasTour Friday, February 21, 2014 10:47 AM
    Friday, February 21, 2014 9:59 AM
  • Thx Philip, you put me in the right direction!

    Regards David


    D

    Friday, February 21, 2014 10:47 AM