Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
MDX Script: How do I create a YTD-Balance Measure?

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

  • Friday, July 21, 2006 2:04 AM
     
     
    Sign In to Vote
    0

    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

     

All Replies

  • Friday, July 21, 2006 5:27 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    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;

     
  • Sunday, July 23, 2006 1:17 PM
     
     
    Sign In to Vote
    0
    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.