Hi MDX guru,
My fact table has 2 Dimensions [Date] and [Province] with 1 measure [Sale].
Not every date has Sale data.
I need your help to formulate a MDX (calculated member) for the following
condition:
If there is no Sale for [date].currentmember,[province].currentmember
Then: display the Sale from the most recent date where Sale is available
(remark : the most recent Date must be smaller than the selected
Date)
Else: just display current Sale

Thanks for your input.

Something like the following will work. Note that this measure is
recursive, it refers back to itself to keep stepping back until if finds
a non-empty Sales amount.

Measures.LastSales
==================
IIF(IsEmpty(Measures.Sales),
(Date.CurrentMember.PrevMember,Measures.LastSales) ,(Measures.Sales))

It Depends a bit on the order of your time dimension. The following
query works against the Foodmart 2000 database, notice that I am using
the .NextMember function, not .Prevmember.

MEMBER Measures.lastSales as 'IIF(IsEmpty(Measures.[Store Sales]),
(Time.CurrentMember.NextMember,Measures.LastSales) ,(Measures.[Store
Sales]))'

SELECT
{Measures.[Store Sales]
,Measures.[lastSales]} ON COLUMNS,
Time.Members ON Rows
FROM Sales
WHERE ([Promotions].[All Promotions].[Bag Stuffers])

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <C9989E26-6823-42D9-991A-B0513B58488F (AT) microsoft (DOT) com>,
TrucH (AT) discussions (DOT) microsoft.com says...