I’ve decided that the best way to spend a day of 104 degree weather is to sit inside and write a blog post!  I’ve been working with MDX quite a bit recently and thought I’d share a few more bits of knowledge I’ve acquired….

I recently received a request to add a new calculated member to a cube that stores financial balance sheet and income statement data.  For those of you who are unfamiliar with financial terms, here’s some definitions for you:

Balance Sheet: A financial statement that summarizes a company’s assets, liabilities and shareholders’ equity at a specific point in time. http://www.investopedia.com/terms/b/balancesheet.asp#ixzz1zmnPhCAg

Income Statement: A financial statement that measures a company’s financial performance over a specific accounting period.
http://www.investopedia.com/terms/i/incomestatement.asp#ixzz1zmndMGYU

The important distinction being that a balance sheet presents a snapshot in time while an income statement summarizes data over a period of time.

In our cube, we have a dimension called Account.  The Account dimension contains an attribute called ‘Account Flag’.  This flag indicates if an account is a Balance Sheet account or Income Statement account. 

The associated members are [Account].[Account Flag].&[BS] and [Account].[Account Flag].&[IS].

We also have a date dimension with the following hierarchy structure, Fiscal Month of Year being the lowest level of granularity:

In addition, our cube contains a measure that summarizes the YTD amount, [Measures].[YTD Amount].  This measure is initially defaulted to the Amount column in the fact table.

The desired aggregation behavior is different depending on the type of account we’re dealing with.  For balance sheet accounts, we want to capture the value from the most recent snapshot (period).  For income statement accounts, we need to sum the data from the first period to the current period in the fiscal year.

This is where Scope, ClosingPeriod and PeriodsToDate come in handy.

First, we must update the cube’s MDX script and default the measure to NULL.

SCOPE([Measures].[YTD Amount]);   
THIS = NULL;   
FORMAT_STRING(THIS) = "Currency";  
END SCOPE; 

The SCOPE statement basically defines what portion of the cube (sub-cube) we want to operate on.  The MDX statements contained within SCOPE will only impact the specified sub-cube, in this case [Measures].[YTD Amount].

Now we need to update the sub-cubes where [Measures].[YTD Amount] intersects [Account].[Account Flag].&[BS] and [Account].[Account Flag].&[IS]. 

Here’s the MDX…

//Balance Sheet

SCOPE([Measures].[YTD Amount],[Account].[Account Flag].&[BS]);   
THIS = IIF(ISEMPTY([Measures].[Amount]),NULL,(CLOSINGPERIOD([Date].[Fiscal].[Fiscal Month Of Year],[Date].[Fiscal].CURRENTMEMBER),[Measures].[Amount]));   
FORMAT_STRING(THIS) = “Currency”;  
END SCOPE; 

//Income Statement

SCOPE([Measures].[YTD Amount],[Account].[Account Flag].&[IS]);   
THIS = IIF(ISEMPTY([Measures].[Amount]),NULL,SUM(PERIODSTODATE([Date].[Fiscal].[Fiscal Year],[Date].[Fiscal].CURRENTMEMBER),[Measures].[Amount]));   
FORMAT_STRING(THIS) = “Currency”;  
END SCOPE; 

For balance sheet accounts, we use the ClosingPeriod function. 

ClosingPeriod ( [ Level_Expression [ ,Member_Expression] ] ) returns the last sibling among the descendants of the specified member at the specified level.

If the current member is [Date].[Fiscal].[Fiscal Quarter Of Year].&[Q1 2012], then ClosingPeriod returns the member [Date].[Fiscal].[Fiscal Month Of Year].&[201203], being March 2012.

If the current member is the year 2012, then ClosingPeriod returns December 2012.

ClosingPeriod provides the desired behavior for balance sheet accounts since we capture the account balance at the last period associated with the specified member.

For income statements, we need to use a combination of Sum and PeriodsToDate. 

PeriodsToDate ( [Level_Expression [ ,Member_Expression ] ] ) returns a set of sibling members at the same level as the specified member.  In the example above, we are summing the amounts for each period in the specified fiscal year.