MDX question

I'm new to MDX and I'm trying figure out how to do the following:

I have a fact table that represents magazine subscriptions. for each subscription, I need to determine if there is a renewal subscription available. I have a hierarchy which consists of MasterSubID, IndividualSubID and TermNumber. MasterSubID represents the lifetime of subscriptions for an individual customer. IndSubID represents each subscription for a customer. TermNumber increments each time a customer orders a subscription, so the first time they subscribe, termnumber is 1, if they renew it would be 2 and so on.

Now, each subscription has an expiredate. For each expire date, I need to find all subscriptions that are expiring on that date. I then need to determine if there is another subscription with the same MasterSubID and a termnumber that is after the current term number. This would be the renewal. I would then need to calculate the percentage renewed by expire date.

The biggest problem I have right now is determining if there is a subscription after the current one.I tried using various functions: nextmember, lead(1), etc. but I don't seem to be getting anywhere.

Any help would be greatly appreciated.

Frank

Thanks.

I think you need to do that in the datatable before you populate the cube, and then create a attribute with the value renewed = true.