MDX Script: How do I create a YTD-Balance Measure?

Hi,

I am using the Standard Edition of SQL Server 2005. I have a financial reporting cube with the measure Amount and several dimensions (Time, Account, etc). A simplified version of the data is:

AccountType

Month1

Month2

Month3

Month4

etc...

Asset

200

20

25

30

Liability

-100

-5

-10

-15

If I use the default cube then this is how data will show. What I would like to do is to add a YTD-Balance measure to the cube. This YTD-Balance measure would show the following data:

AccountType

Month1

Month2

Month3

Month4

Asset

200

220

245

275

Liability

-100

-105

-115

-130

So instead of showing the difference in a month (eg 20) like the underlying data, I would like to show a running total for the year (eg 200+20=220). My attempt so far is:

Create Member CurrentCube.[Measures].[YTDBalance]
AS SUM({PeriodsToDate([Time].[(All)])}, [Measures].[Amount]),
NON_EMPTY_BEHAVIOR = { [Amount] },
VISIBLE = 1;

This doesn't do what I want however: it sums all months in a quarter, all quarters in a year, and all years in the cube. What I instead want is for each month to be a summary of the months in the year so far. Is anyone able to help me out at all with this?

Thanks, Matt

Assuming that there is a [Year] attribute in the [Time] dimension, whose type is "Years"; and that there is a [Time].[Calendar] hierarchy (as in the Adventure Works Date dimension), then you should be able to use the YTD() function:

Create Member CurrentCube.[Measures].[YTDBalance]
AS SUM(YTD([Time].[Calendar].CurrentMember), [Measures].[Amount]),
NON_EMPTY_BEHAVIOR = { [Measures].[Amount] },
VISIBLE = 1;

|||A word of caution: You should only set the NON_EMPTY_BEHAVIOR if you are certain that there actually is a value in [Measures].[Amount] for the cells in which you want to display YTDBalance. Example: If you wanted to show the YTDBalance for all months in 2006, but you only had data up until July 2006, the calculated member suggested by Deepak would only return values for YTDBalance up until July 2006.