locked
Filtering Measures RRS feed

  • Question

  • How would i write a MDX for a Date Range Sales Analysis.

    The result i am looking from Adventure Works, for our example we can hard code the date, later i would substitute it with VBA!NOW() function.

    Measure    Current (0-30Days)    Prior(31-60 Days)     Change%

    ----            ---------------------       -------------------   -------------

    Sales                   $$                     $$                            %

    Quantity

    other measu

     

    Thank You

     

    Wednesday, June 2, 2010 7:27 PM

Answers

  • Are you going to use it in a SSRS report? If so, you can do this in a matrix where on the rows you can hardcode Sales, Quantity, etc and in the cell section, you can put the appropriate measure for Sales, Quantity etc 


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    • Proposed as answer by Jerry Nee Friday, June 4, 2010 9:57 AM
    • Marked as answer by Farrukh Lala Friday, June 4, 2010 2:12 PM
    Thursday, June 3, 2010 6:25 AM

All replies

  • I got the single above part of the problem, but i dont know, how to show label "Sales Amount" on rows and how to add Order Quantity to along with sales and show the prior data. Any ideas guys

     

    With Member [Measures].[Current] as
    			([Measures].[Sales Amount], 
    			[Date].[Date].&[20040630].lag(30))
    Member		[Measures].[Prior] as
    			([Measures].[Sales Amount],
    				[Date].[Date].&[20040630].lag(30*2))
    Member		Measures.[Difference] as
    			[Measures].[Prior]-[Measures].[Current]
    Member		[Measures].[Change] as
    			([Measures].[Difference]/([Measures].[Current])*100),
    			Format_String = "Percent"
    
    Select {
    
    	[Measures].[Current],
    	[Measures].[Prior],
    	[Measures].[Difference],
    	[Measures].[Change]
    	}	 on 0,
    	
    
    From [Adventure Works]
    
    Wednesday, June 2, 2010 9:33 PM
  • Are you going to use it in a SSRS report? If so, you can do this in a matrix where on the rows you can hardcode Sales, Quantity, etc and in the cell section, you can put the appropriate measure for Sales, Quantity etc 


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    • Proposed as answer by Jerry Nee Friday, June 4, 2010 9:57 AM
    • Marked as answer by Farrukh Lala Friday, June 4, 2010 2:12 PM
    Thursday, June 3, 2010 6:25 AM