ParallelPeriod that never returns null

I'm sure this is easy, but I haven't been able to cook up the secret sauce: I need a function like ParallelPeriod that will always return a member (unless I'm asking for a member that predates the oldest member in the dimension, when null is perfectly fine).

For example, ParallelPeriod([Month],1,[2006].[December].[31]) returns null. Instead, I want to get back [2006].[November].[30] (the last day of the month).

What's the secret?

There are many ways to do it. One way is to have an attribute which keeps number of days needed to go back to get to the day in the previous month. Then you will use

Date.CurrentMember.Lag(Date.DaysToGoBack.MemberValue)

for December 30 it will be 30, and for December 31 it will be 31 - so both of them will land on November 30.

|||How 'bout a way that doesn't involve changing the definition of the cube?|||

Possible too, but it won't have as good performance as the solution I proposed first. Again, there are many ways, but they revolve around the following theme (note that the formula below is only valid for the Month level. I am not sure what you want to do on the other levels).

IIF(Date.CurrentMember IS Date.CurrentMember.Parent.LastChild, Date.CurrentMember.Parent.PrevMember.LastChild, ParallelPeriod(...))

|||

OK, I see the pattern. The solution you propose above won't quite work (think of March), but it's close enough to something that will work to see where to take it.

If I decide that I really need this function, I'll probably go with the first suggestion you made, since I can still change the cube design at this point, I was just looking for a wider variety of options.

Thanks for the suggestions!