Measures and Measure Groups - Expert Cube Development with SSAS Multidimensional Models - page 90

Another similar modeling problem you may encounter is the need to return the
last ever non-empty value of a measure from the beginning of time up to the last
date in the currently selected time period. An example of this might be where you
wanted a calculated measure to display the value of the last sale made to a particular
customer: if this last sale took place in August and you were looking at December,
a LastNonEmpty measure would return null; what you need to do is to find the last
sale value from the very first date in the Time dimension up to the end of December.
The most efficient way to solve this problem in MDX is described in the following
blog post: http://tinyurl.com/LastEverNE .

ByAccount
A chart of accounts dimension is a feature common to many cubes containing
financial data; in many ways it has a lot in common with the measures dimension,
because each member on a chart of accounts dimension represents a different type of
value, such as profit and loss or balance sheet values. A chart of accounts dimension
is often implemented with a parent/child hierarchy, and there are often complex
business rules that govern how the members on the hierarchy should aggregate up,
if they even do at all. Analysis Services provides several features to help you build
and manage such a dimension, although it's widely accepted that building financial
applications in Analysis Services is not as easy as it should be, or indeed as easy as it
is in other OLAP servers.

The ByAccount aggregation type (available in Enterprise Edition only) is the first
of these features we'll discuss, and it allows us to define different semi-additive
behavior for a single measure for different members on the main hierarchy of a
chart of accounts dimension. The steps to get it working are as follows:

1. Create your chart of accounts dimension.
2. Create another attribute that will be used to flag which members on the main
hierarchy will use which semi-additive aggregation type. This should have one
member for each type of attribute, where each member represents a different
form of semi-additive behavior. Call it something like Account Type .

3. Set the following properties:

° On the dimension itself, set the Type property to Accounts
° On the main hierarchy, set the Type property to Account
° On the Account Type attribute, set the Type property to
Account Type