none
MDX Measure * Total Measure

    Question

  • Company-Branch-Client -> Income -This FY-This Month

    vs

    Company-Branch-Client -> Sum Income for Last FY

     

    with 
    member [Measures].[LastFY] as
    sum([Last FY by Invoice Date],[Measures].[Income])
    
    member [Measures].[ThisFYThisMonth] as
    sum(([Current FY],[Date].[Month of Year].&[1]]),[Measures].[Nett Income])
    
    
    select non empty
    {[Measures].[ThisFYThisMonth],[Measures].[LastFY]}
    
     on 0,
    non empty
    
    {[Company].[Company].Members}*
    {[Branch].[Branch].Members}*
    {[Client].[Client].Members}
    
    on 1
    from [cube]
    
    the problem is that I got all clients  vs all clients, I need just compare Income this month vs Total Income of full lats year
    Monday, September 27, 2010 2:04 AM

Answers

  • Hi ArtemB,

    Do you mean that you want to see the total for all clients against each client (per company and branch only)?

    If so, you can write:

    with 
    member [Measures].[LastFY] as
    sum({[Last FY by Invoice Date]*[Client].[Client].[(All)].Item(0)},[Measures].[Income])
    
    member [Measures].[ThisFYThisMonth] as
    sum({{[Current FY],[Date].[Month of Year].&[1]}*[Client].[Client].[(All)].Item(0)},[Measures].[Nett Income])
    
    
    select non empty
    {[Measures].[ThisFYThisMonth],[Measures].[LastFY]}
    
     on 0,
    non empty
    
    {[Company].[Company].Members}*
    {[Branch].[Branch].Members}*
    {[Client].[Client].Members}
    
    on 1
    from [cube]
    This will effectively ignore the slice by Client and will give you the total amount for all clients sliced by company and branch.

    Boyan Penev --- http://www.bp-msbi.com
    Monday, September 27, 2010 2:42 AM