Workaround for absence of semi-additive function LastNonEmpty in SQL Server 2005 Standard Edition

In my last project I'd met problem when I tried to setup OLAP cube with semi-additive measure on Analysis Service Standard Edition. In my case LastNonEmpty.

As example database I use Adventure Works DW.

Measure [Last NonEmpty Total Product Cost - Workaround] was created under [Sales Summary] measure group with Sum aggregate function. Then in Calculations added next cell calculation member.

 

CREATE CELL CALCULATION [Adventure Works].CalculateLastNonEmpty 
    FOR '({[Measures].[Last NonEmpty Total Product Cost - Workaround]}, DESCENDANTS([Date].[Calendar].[All Periods]))' 
    AS 
' 
    Aggregate( 
        Tail( 
            Filter( 
                Descendants( 
                    [Date].[Calendar].CurrentMember, 
                    [Date].[Calendar].[Date] 
                ) , 
                CalculationPassValue([Measures].[Last NonEmpty Total Product Cost - Workaround], 0) <> null 
            ),
            1
        ), 
        CalculationPassValue([Measures].[Last NonEmpty Total Product Cost - Workaround], 0) 
    ) ' 

After this result of [Measures].[Last NonEmpty Total Product Cost - Workaround] will be similar LastNonEmpty aggregate function.