Hello,

I have a variant of the common stock problem.
I have a cube to present the number of apples per store at any time,
thus containing the time dimension (year, month, day), store dimension
(city, store) and a measure Nr of Apples.

The mdx for the calculated measure Nr of Apples is:

(Tail(Filter(Descendants([Time].[Gregorian].CurrentMember,[Day]),Not
([Measures].[Nr of Apples]) =NULL),1).Item(0),[Measures].[Nr of
Apples])

However, with the following data:
"city" "store" "date" "nr of apples"
F A 10-08-2005 10
F B 10-08-2005 5
F C 10-08-2005 15
F A 17-08-2005 15
F B 17-08-2005
F C 17-08-2005 10

something goes wrong. When I look at the nr of apples for city a on the
year level of the time dimension it shows 25 (being the last known
situation) correctly. However, when i drill down to store level it
shows A: 15 (correct), B: 5(incorrect!!), C: 10 (correct).

So what the formula does is taking the last known value for which it
has data. But in this case i'd like it to take the value for the last
known date.

Does anybody have a solution??
Thnx in advance.

Carlos

Try

(Tail(Filter(Descendants([Time*].[Gregorian].CurrentMember,[D*ay]),([Measures].[Nr
of Apples])>0 ),1).Item(0),[Measures].[*Nr of
Apples])

That gives the same result. The problem is that it shouldnt take the
10-08-2005 data for store B (being the last non empty value) but the
17-08-2005 data, being the date of the overall (all stores combined)
last non empty value.

What i understand is if you use Tail and Filter it always shows the
last nonempty values you need to change the formula us Coalsce empty
MDX function with current member of time

Regards
Prasanna

Maybe this is it:

Tail(Filter( [Day].Members ,Not IsEmpty( ( [Measures].[Nrof
Apples],[Store].[All Stores] )))).Item(0)

HTH,
--
Brian Altmann
BI Specialist
Huddle Group S.A (www.huddle.com.ar)
www.geocities.com/brianaltmann/olap.html


"Galactico" wrote:

All,

Always use suffix {set}.item(0).item(0) to turn a set into a member, using
only .item(0) will turn the set into a tuple, which may work sometimes, but
not always.

HTH,

Jim


"Brian Altmann" wrote: