crossjoin problem?

i need to calculated the number of participants during a specific period for a give club. the member table contains the following info: member id, club id, starting date, ending date, etc. i have created four named calculations: starting year, starting month, ending year, ending month. starting year and starting month are part of the hierarchy starting period while ending year and ending month belong to the hierarchy ending period.

i defined a basic measure: count and a calculated measure: numberofparticipants. the calculated measure is defined as:

sum(crossjoin([starting period].currentmember.parent.firstsibling.firstchild:[starting period].currentmember, [ending period].currentmember:[ending period].currentmember.parent.lastsibling.lastchild, [club id].currentmember), [measures].[count]))

i could use nonempty crossjoin but since the hierarchies are from the same dimension, it doesn't make a difference.

the MDX query i have looks something like:

select [starting period].[2005].[x] on axis(0), [ending period].[2005].[x] on axis(1) from clubstats where ([measures].[numberofparticipants], [club id].[23])

i am hoping to get the number of participants in club 23 during the month x of 2005. the problem is that for some months, the query returns correct result and for other months. the months that do have a problem are those where there is no existing record in the table with an ending date in month x even if there are records with ending date in month x+1, x+2, etc. it seems that crossjoin or the query "bailed out" after it determines that tuple (x, x) does not exist.

if i use different months for starting and ending period, e.g.

select [starting period].[2005].[3] on axis(0), [ending period].[2005].[4] on axis(1) from clubstats where ([measures].[numberofparticipants], [club id].[23])

again the result is empty if no participant with starting month of 2005 3 and ending month of 2005 4 even if there is participant starting in 2005 3 and ending in 2005 6, which ought to be part of the result.

does anybody know why this is happening? any help will be greatly appreciated.

I found the cause of the problem. Since the dimension itself is based on the fact table, (x, x) or (x, y) may not correspond to any valid dimension member. To get around this, I need to create two server generated time dimension and use starting date and ending date to setup regular relationship between the measure and these dimensions.

I am still interested in hearing recommendations from experts on this forum because there maybe more elegant/efficient ways to construct cube/dimension/calculated measures to get the count.