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

Note that the semi-additive measures only have an effect above the lowest level
of granularity on a Time dimension. For dates such as July 17 in the preceding
screenshot, where there is no data for the Sum measure, the LastNonEmpty
measure still returns null and not the value of the last non-empty date.
The semi-additive measure aggregation types (and None ) are only available in
Enterprise Edition; it's possible to recreate the same functionality using MDX, but
query performance will not be quite as good. Here's an example of how you could
overwrite the value of a measure with the AggregateFunction Sum property
by using an MDX Script assignment to make it behave in the same way as a
LastNonEmpty measure:
SCOPE([Measures].[Sales Amount]);
THIS = TAIL(
NONEMPTY(
{EXISTING [Date].[Date].[Date].MEMBERS}
* [Measures].[Sales Amount])
,1).ITEM(0);
END SCOPE;
We may also find that the performance of LastNonEmpty is still not good enough,
especially on very large cubes. It is not as fast as LastChild , so one trick we could
try is to calculate the last non-empty value for a measure in a new column in your
fact table of your ETL. We can then create a new measure from this column, use the
LastChild measure as its aggregation type, then set its Visible property to False
and use an MDX Script something like the following to display the new measure's
value preceding the leaf level for the original measure:
SCOPE([Measures].[Sales Amount], [Date].[Date].[All]);
THIS = [Measures].[LastChild];
END SCOPE;
This assignment forces each time period to show the value of the last date within that
time period. Typically, though at the tail of your Date dimension you will have dates
that have not occurred yet and which cannot contain any data—for example, if today's
date is April 16, 2013, you might only have data in the cube up to April 15, but the
Date dimension will probably contain all of the dates up to December 31, 2013. For
these future dates, you'll either have to ensure your ETL populates the value of the
LastChild measure up until the end of the current year or use another MDX Script
assignment that is similar to the one described at the end of the following blog entry:
http://tinyurl.com/gregsemiadd .