none
How to filter calculated measure ?

    Question

  • Hello,

    I want to filter my calculated measure by previous member of filtered item.


    for an example I need June-2013 amount when I select July-2013 of the filter.

    How can I accomplish this task ?

    Thanks in advance.

    Sunday, July 28, 2013 6:16 AM

Answers

  • Hi

    I haven't tested it, but seems simple like that only

    alternatly try this using direct prevMember

    SCOPE ([Date].[Calendar].[Month].Members,[Measures].[Internet Order Count] );
    
    this = ([Date].[Calendar].prevMember , [Measures].[Internet Order Count]);
    
    END SCOPE;


    Prav

    Monday, July 29, 2013 5:49 AM

All replies

  • Hi

    You need to use prevMember on filter member

    try this

    SELECT [Measures].[Internet Order Count] ON 0
    ,
    StrToMember("[Date].[Calendar].[Month].&[2008]&[7]"  -- Parameter July 2008
                , CONSTRAINED).prevMember ON 1
    FROM [Adventure Works]


    Prav

    Sunday, July 28, 2013 8:23 AM
  • Hi Prav,

    Thank you for your reply . but it's not what I wanted. I cannot hard code exact month in the calculated measure. I want previous month value when filtered by any member of date dimension.

    Sunday, July 28, 2013 9:54 AM
  • Hi

    I thought your going to use reporting tool for this scenario,

    try this in cube calculations

    SCOPE ([Date].[Calendar].[Month].Members,[Measures].[Internet Order Count] )
    
    this = ([Date].[Calendar].CurrentMember.prevMember , [Measures].[Internet Order Count])
    
    END SCOPE;
    let me know your results


    Prav

    Sunday, July 28, 2013 11:18 AM
  • Thanks,

    I couldn't get any results because when I process the cube it says The syntax for SCOPE is incorrect

    Do you have any suggestions?

    Sunday, July 28, 2013 12:29 PM
  • Hi

    My bad, I can see a syntax typo (requires ;  at ending)

    Try this

    SCOPE ([Date].[Calendar].[Month].Members,[Measures].[Internet Order Count] );
    
    this = ([Date].[Calendar].CurrentMember.prevMember , [Measures].[Internet Order Count]);
    
    END SCOPE;

    let me know your results


    Prav




    • Edited by Prav_SQL Monday, July 29, 2013 2:17 AM removed comments
    Sunday, July 28, 2013 10:06 PM
  • Hi,

    Thanks for your reply again. but still I got same error.

    Monday, July 29, 2013 4:23 AM
  • Hi

    Can you let me know how you are integrating this SCOPE and give me your MDX query if possible


    Prav

    Monday, July 29, 2013 5:08 AM
  • Hi,

    I used exactly above query in calculated measure of ssas cube. 

    I've changed my dimension and measure only.
    • Edited by Anush87 Monday, July 29, 2013 5:33 AM edit
    Monday, July 29, 2013 5:26 AM
  • Hi

    I haven't tested it, but seems simple like that only

    alternatly try this using direct prevMember

    SCOPE ([Date].[Calendar].[Month].Members,[Measures].[Internet Order Count] );
    
    this = ([Date].[Calendar].prevMember , [Measures].[Internet Order Count]);
    
    END SCOPE;


    Prav

    Monday, July 29, 2013 5:49 AM