# Sum of YTD values for selected month • ### 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

• 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 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 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(
(
[Set Of Months]
,Rank
(
[Date].[Calendar].CurrentMember
,[Set Of Months]
)
),
YearToDate
),
FORMAT_STRING='CURRENCY'
MEMBER Avg_YearToDate AS
SumOfYTD/
(
[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
WHERE
[Date].[Calendar Year].&```

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