last non null value

[Greg]

With a DMR model I have a crosstab with countries on the row edge and years on the column edge.  I need to come up with an expression that returns the value for the last year that has a non null value for each country.

E.g.
      2007   2008   2009   2010   Last Non Null Value
      -------------------------------------------------------------------------------
Canada   100      200      250            250
US      200      300      450      500      500
UK      50      300                  300

Any ideas?

Thanks.

Greg

[Greg]

I figured it out  ;D

topCount (
  [Year],
  1,
  if ( value( tuple( currentMember( hierarchy( [Year] ) ), [Measure] ) ) is null )
  then ( 0 )
  else ( roleValue( '_businessKey', [Year] ) )
)

[barrysaab]

Thanks for sharing the solution.

[Greg]

It can actually be further simplified.

topCount (
  [Year],
  1,
  if ( [Measure] is null )
  then ( 0 )
  else ( roleValue( '_businessKey', [Year] ) )
)