MDX: Last nonempty

Last non empty

LastNonEmpty is an aggregation function available in the Enterprise version of SQL Server. However, you can create your own with a little bit of recursive MDX. Essentially, you simply create a calculated member that returns the non empty value, or if empty, it looks in the previous member. It really is very simple, and is an elegant way of writing a last non empty query. To create a LastNonEmpty calculate measure, simple use MDX such as the following


With Member Measures.LastHits as
iif(isempty(Measures.Hits),
([Date].[Year Month Day].prevmember,
Measures.LastHits
),Measures.Hits)


Notice how the calculated member is called LastHits, and the calculation actually refers to LastHits. This is the recursion. It keeps referring back to itself until it gets a day with a non empty value. If there are no non empty values, the recursion will automatically stop at the first day in your dimension and return null.


Naturally, you can add other calculations on top of this recursive calculation. For example, you might want to sum up the month to date figures using this non empty calculation. No problem, here is a MTD calculation using the last non empty calculation above.

With Member Measures.LastHits as
iif(isempty(Measures.Hits),
([Date].[Year Month Day].prevmember,
Measures.LastHits
),Measures.Hits)
Member Measures.LastHitsMTD as
sum(PeriodsToDate([Date].[Year Month Day].[Month],[Date].[Year Month Day]),Measures.LastHits)
select
{Measures.Hits,Measures.LastHits,Measures.LastHitsMTD}
on Columns,
tail([Date].[Year Month Day].[Day],31)
on rows
from
EasternMining