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
I figured it out ;D
topCount (
[Year],
1,
if ( value( tuple( currentMember( hierarchy( [Year] ) ), [Measure] ) ) is null )
then ( 0 )
else ( roleValue( '_businessKey', [Year] ) )
)
Thanks for sharing the solution.
It can actually be further simplified.
topCount (
[Year],
1,
if ( [Measure] is null )
then ( 0 )
else ( roleValue( '_businessKey', [Year] ) )
)