locked
Calculated measure problem with MDX over tuples RRS feed

  • Question

  • Hi;

    I need to look at a tuples total value over a measure. Like (ParallelPeriod(Day,30,Time.Currentmember),Store.currentmember,Measures.[SalesAmount]). I'm looking at a spesific store's sales at 30 days before. There is a report which has stores on rows that runs correctly. But when I do the same thing with Products on rows it calculates like (ParallelPeriod(Day,30,Time.Currentmember),Store.currentmember,Measures.[SalesAmount],Product.CurrentMember) on all rows. How can I escape this thing? I tried (ParallelPeriod(Day,30,Time.Currentmember),Store.currentmember,Measures.[SalesAmount],Product.[All]) but it didn't run. That time it didn't look at the store.currentmember. 


    BI Developer
    Thursday, December 30, 2010 10:15 AM

Answers

  • Why dont you use simply the Lag function...Anyways i have included both ways...

    WITH 
     member [30DaysBack] as
     ([Date].[Date].currentmember.lag(30),[Measures].[Internet Sales Amount]) 
    
    member [30DaysBackUsingParallel] as
     (
    	(ParallelPeriod([Date].[Date].[Date],30,[Date].[Date].Currentmember),[Measures].[Internet Sales Amount])
     ) 
    
    member [30DaysBackUsingParallelAllSubCategoryAllProducts] as
     (
    	(	[Product].[Subcategory].[All Products],[Product].[Product].[All Products],
    	(ParallelPeriod([Date].[Date].[Date],30,[Date].[Date].Currentmember),[Measures].[Internet Sales Amount]))
    	
     ) 
     
    SELECT {[Measures].[Internet Sales Amount],[30DaysBack],[30DaysBackUsingParallelAllSubCategoryAllProducts]} ON 0 ,
    nonempty([Date].[Date].&[594]*
    [Product].[Category].[Category]*[Product].[Subcategory].[Subcategory]*[Product].[Product].members,[Measures].[Internet Sales Amount]) on 1 
    FROM [Adventure Works]
    

    vinu
    • Marked as answer by Raymond-Lee Friday, January 7, 2011 2:27 AM
    Thursday, December 30, 2010 6:35 PM