Obtaining a measure based on date in MDX

Question

These are really hard to describe without going in-depth into the data structure, i'll try to keep it as short as possible while giving as much info as I can.

I have a measure which is a monetary value over time (cashflow), along with dimensions for date, and one for valuation. A valuation has a date, and this date would correspond with the first cashflow date.

The gist of what im after is the first value (or in other words the cashflow value corresponding with the valuation date. If I know the valuation date, it is easy enough to turn this date into the Date dimension to get me the value im after:

WITH MEMBER [Measures].[Value at Valuation Date]
AS
(
    [Measures].[Value], 
    [Date].[Date].&[2009-09-30T00:00:00]
)

However, if I have a set of valuation dates (say, im trying to get the value at valuation date for 3 month's wort of valuations) this method breaks down, as earlier valuation would have a value corresponding with the date of a later valuation.

So for example this calculated member will return the wrong values:

WITH MEMBER [Measures].[Value at Valuation Date]
AS
(
    [Measures].[Value], 
    {[Valuation.[Valuation Date].&[2009-07-31T00:00:00]:[Valuation].[Valuation Date].&[2009-09-30T00:00:00]}
)

In this example, there are 3 valuations (end of july, aug & sept), each with monthly cashflow values going forward (on the month ends) from the corresponding valuation date. Rather than return me the first cashflow value for each valuation, this gets a sum of all cashflow values which happen to correspond to a valuation date.

I hope this is clear enough to start some discussion of this problem. In case its useful, this is a SQL query that would get the right data from my cube staging database:

SELECT 
    v.valuationDate,
    SUM(cf.value) as [value at valuation date]
FROM dbo.Valuation v
INNER JOIN dbo.Cashflow cf
ON v.id = cf.valuationId
and v.valuationDate = cf.cashflowDate
GROUP BY v.valuationDate

Thanks

Answer 1

When you pust a set into a calculated member you are implicitly asking for the measure to be aggregated.

In order to get the equivalent to your SQL query you would have to write an MDX query which asked for a set of dates on the rows.

eg.

SELECT
  Measures.[Value] on columns,
  {[Valuation.[Valuation Date].&[2009-07-31T00:00:00]
  :[Valuation].[Valuation Date].&[2009-09-30T00:00:00]} on rows
FROM [<Cube Name here>]

But if you don't know the particular date range and just want to get the first value you could do the following:

NONEMTPY([Value].[Valuation Date].[Valuation Date].Members * Measures.Value).Item(0)

Which should be much faster than using the Filter() function

Answer 2

I did work out the answer to this in the end, and just in case its usefuil to someone else here it is.

I ended up with a calculated member which loks much like this:

([Measures].[Value], 
HEAD(
      FILTER([Date].[Date].Members, Not IsEmpty([Measures].[Value])), 
      2).Item(1))

If you recall that I needed the first "Value" for a given set of dates.