Help to create calculated measure based on Effective Date

My fact table and dimension (simplified) looks like this:

Fact Table
Product ID, DateID, Price
P1, 01-Jan-2008, $10
P1, 01-Feb-2008, $15
P1, 15-Apr-2008 $17

Product Dimension
ProductID, Product Name

Effective
EffectiveDateID
DateDisplay

I have built a cube based on this. Now i need to create a calculated
measure called Effective Price which will give the price for a given
day. Fo example: it should be $10 for 05-Jan-2008, $15 for 15-Feb-2008
and $17 for 15-Apr-2008. Any help to create this calculated measure
will be very much appreciated.

I have used the aggregation of type 'Last Non-Empty Child' for Price
and it is giving the latest price when i choose all dates.

Thanks
Mahesh

Try someting like:

([Measures].[Price],
Tail(NonEmpty({Null
:[EffectiveDate].[EffectiveDateID].CurrentMember},
[Measures].[Price])).Item(0))


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***