First Non Empty value for a given date range

Question

I want to get the first non empty measure value for a given date range using the following calculated member:

(
 [Measures].[Total],
 Head
  (
   Nonempty
   ( [Date].[Date].members
    ,[Measures].[Total]
   )
  ).Item(0)
)

The calculated member is working fine for all the dates in the cube, however when I filter the query and add date range, it still gets the last non empty measure for all the dates, not the selected dates only. Any suggestions to overcome this problem?

Answer 1

Try forcing context with EXISTING:

(
 [Measures].[Total],
 Head
  (EXISTING
   Nonempty
   ( [Date].[Date].members
    ,[Measures].[Total]
   )
  ).Item(0)
)

Also I think you want to use TAIL to get the last date with data rather than HEAD which gets the first in the set:

(
 [Measures].[Total],
 Tail
  (EXISTING
   Nonempty
   ( [Date].[Date].members
    ,[Measures].[Total]
   )
  ).Item(0)
)

To continue using Head you'd need to order the inner set first - ORDER is slow so this is best avoided:

(
 [Measures].[Total],
 Head
  (EXISTING
     ORDER(
       Nonempty
       ( [Date].[Date].members
        ,[Measures].[Total]
       )
     ,[Date].CURRENTMEMBER.MemberValue
     , BDESC
     )
  ).Item(0)
)