locked
Sum of YTD values for selected month RRS feed

  • Question

  • Hi there,

    I have the following requirement and no idea how to solve it - would be great if someone could help me out:

    • The requirement is to create a MDX key figure for calculating the sum of YTD values for a given month, e.g. for calculating average inventories over time.
    • To keep it simple, let's assume I have the dimensions 'Fiscal period' (Time), 'Account' and one key figure 'Quantity' (which shows the movements per month, not the final stock)
    • I know there is the YTD() function in MDX, but it only gives me the YTD value for the current month.

    Example: The user wants to see the average inventory for month April:

    1. January: Amount = 100 --> YTD(January) = 100
    2. February: Amount = 150 --> YTD(February) = 100 + 150 = 250
    3. March: Amount = 200 --> YTD(March) = 100 + 150 + 200 = 450
    4. April: Amount = 170 --> YTD(April) = 100 + 150 + 200 + 170 = 620

    Since April is the fourth month of the year, the average inventory would now be calculated as:

    • (YTD(January) + YTD(February) + YTD(March) + YTD(April)) / 4 => (100 + 250 + 450 + 620) / 4 = 355

    This is the value which should be shown by the key figure if the user selects April as fiscal period and inventory as account. Can anyone explain me how to achieve this? Any help is highly appreciated. Thanks in advance!

    Wednesday, February 29, 2012 2:45 PM

Answers

  • I would do something similar to this:

      Sum
      (
          [Date].[Calendar].CurrentMember.FirstSibling
        : 
          [Date].[Calendar].CurrentMember
       ,[Measures].[Internet Sales Amount]
      )

      Count
      (
          [Date].[Calendar].CurrentMember.FirstSibling
        : 
          [Date].[Calendar].CurrentMember
      )

    I'm assuming that your hierarchy is Year > Month > Day. 



    Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

    • Marked as answer by Mothman3000 Thursday, March 1, 2012 3:34 PM
    Wednesday, February 29, 2012 7:35 PM

All replies

  • I would do something similar to this:

      Sum
      (
          [Date].[Calendar].CurrentMember.FirstSibling
        : 
          [Date].[Calendar].CurrentMember
       ,[Measures].[Internet Sales Amount]
      )

      Count
      (
          [Date].[Calendar].CurrentMember.FirstSibling
        : 
          [Date].[Calendar].CurrentMember
      )

    I'm assuming that your hierarchy is Year > Month > Day. 



    Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

    • Marked as answer by Mothman3000 Thursday, March 1, 2012 3:34 PM
    Wednesday, February 29, 2012 7:35 PM
  • check if this helps

    With 
    SET [Set Of Months] AS
    	EXISTING
    	(
    		[Date].[Calendar].[Month]
    	)
    	
    MEMBER YearToDate AS
    	SUM(
    		YTD(
    			[Date].[Calendar].CURRENTMEMBER
    		),
    		[Measures].[Internet Sales Amount]
    	)
    MEMBER SumOfYTD AS 
    SUM(
    	HEAD
    	(
    		[Set Of Months]
    		,Rank
    		(
    			[Date].[Calendar].CurrentMember
    			,[Set Of Months]
    		)
    	),
    	YearToDate
    ),
    FORMAT_STRING='CURRENCY'
    MEMBER Avg_YearToDate AS
    	SumOfYTD/
    	HEAD
    	(
    		[Set Of Months]
    		,Rank
    		(
    			[Date].[Calendar].CurrentMember
    			,[Set Of Months]
    		)
    	).count
    SELECT 
    {
    	 [Measures].[Internet Sales Amount]
    	,[YearToDate]
    	,[SumOfYTD]
    	,[Avg_YearToDate]
    } ON 0,
    [Date].[Calendar].[Month] ON 1 
    FROM
    	[Adventure Works]
    WHERE 
    	[Date].[Calendar Year].&[2005]


    vinu

    Thursday, March 1, 2012 8:50 AM
  • Wow - brilliant, works like a charm :)

    Now as I read your code, it seems perfectly logical to me... but the difficult thing is to work those things out by oneself.  Thanks a lot for your help!

    Thursday, March 1, 2012 3:34 PM
  • Just want to clarify that the post that you have marked as answered does not really get you the result that you have put up in your formula in first post.

    Example: For what you want for Feb is ( YTD(Jan) + YTD(Feb) )/ 2 which is 100+250=350/2=175

    what the query gives is 100+150/2=250/2=125

    Similary Mar = 100+250+450/3

    what the query gives is 100+150+200/3

    wrt to Adventure works it gives 473+506+473=1453/3=484


    vinu

    Thursday, March 1, 2012 5:33 PM