As I said, it works fine in Custom Rollup Formula.

I tried to apply your calculation in Calc Measure:

iif(
IsLeaf( [DateDimension].CurrentMember),
CoalesceEmpty((Measures.[Value]) , (Measures.[Last Non Empty
Value],[DateDimension].PrevMember)),
([Measures].[Last Non Empty Value],
Tail(Descendants([DateDimension].CurrentMember,,LEAVES)).Item(0))
)

When CurrentMember is not leaf and hasn't any children it returns NULL. So,
I appended calculation with checking non-leaf member to hold NULL value:

iif(
IsLeaf( [DateDimension].CurrentMember),
CoalesceEmpty((Measures.[Value]) , (Measures.[Last Non Empty
Value],[DateDimension].PrevMember)),
CoalesceEmpty(([Measures].[Last Non Empty Value],
Tail(Descendants([DateDimension].CurrentMember,,LEAVES)).Item(0)),
([Measures].[Last Non Empty Value],
Tail(Descendants([DateDimension].CurrentMember.PrevMember,,LEAVES)).Item(0))
)
)

At that case, than non-leaf member hasn't any children i returns PrevMember
Value, but if PrevMember holds NULL value it also returns NULL. It means,
what recursion for non-leaf Members works only by one step back...


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Can you give an example of "when CurrentMember is not leaf and hasn't
any children" - when a member has no children, shouldn't it be a leaf
member?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I mean it doesn't have children with data. For example there are no any
sales on January 1 and January 2 (Hours for these days contain NULL measure
values)

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Since I don't quite follow what's happening in your specific data,
here's a similar example for Foodmart Sales cube. Suppose that [Last
Sales] returns the last non-null [Unit Sales] at the leaf (month) Time
level. In this query, [1998].[Q1] is a non-leaf node with NULL [Unit
Sales]; but [Last Sales] returns the last non-null month's [Unit
Sales], which is 26,796:

'iif(IsLeaf([Time].CurrentMember),
CoalesceEmpty(Measures.[Unit Sales] ,
(Measures.[Last Sales], [Time].PrevMember)),
([Measures].[Last Sales],
Tail(Descendants([Time].CurrentMember,,LEAVES)).Item(0)))'

select {[Unit Sales], [Last Sales]} on columns,
DrillDownLevel({[Time].[1997].[Q4],
[Time].[1998].[Q1]}) on rows
from Sales

- Deepak



gNM wrote: