Next Measure

Lets say I have a set of measures that go like this:

Date, Payment

October 3, $3

October 6, $4

December 6, $5

December 8, $4

My problem is that I need to get the next payment date and amount. So if for example I am standing in October 3, I would have to get (October 4, $4). If I was standing in october I would have to get (December, $9). Assuming I have a measure called [Payment] and a [Date] dimension ([Year], [Month], [Day]), How can I do this with MDX?

Thanks in advance

There are two ways to do it, one is recursive and another one is not. It is hard to judge which one will perform better, both of them have their problems:

1. CREATE NextPayment = IIF(IsEmpty(Time.NextMember) AND NOT (Time.NextMember IS NULL), (Time.NextMember, Measures.Payment), Time.NextMember);

2. CREATE NextPayment = (Tail(NonEmpty(Time.NextMember : NULL, Measures.Payment), 1).Item(0), Measures.Payment)

|||

The MDX is working fine for the amount, but is any way to get the date as a column?

Thanks

|||How can I ignore the current Date context. I mean, how can I calculate those members in the context of every member in the Date dimension and not with the date context specified by the user|||These formulas do position on the right date first, so you can modify them to do whatever else you need to do with the date. I did not understand the second question.|||

Thanks for your response, these formulas works fine when the year is selected, eg. [Year].&[2006] but in the real report the year and month are going to be selected, thats mean that the query is going to search only in the month selected, is correct this? And what happens if whe are in December06 and the next payment is in January07? will get the same problem for filter the cube in another year?

Thanks again for your help

|||Both formulas look for the next time period in the Time hierarchy. If the current time period is Year - they will go to the next Year, if it is Month - they will go to the next month. Jan07 is the next month after Dec06, so there is no problem with it.