MDX HELP Pls

[nachikethm]

We have the following Dimensions and Fact tables:

1. DimDate
2. DimHouse(It can have both Active and Prospect House holds)
3. FactAtive this contains number of bookings
4. FactPropect

We need to write a MDX query to get sum of booking for every month from the context of the prospect fact table. So if we look at jan 2007 we want to get the sum of booking from the FactActive Table, but for period in the future, for example lead of two months, meaning March 2007 in the factActive table, but for all house hold in the Factprospect table. In sql we would do a sub query which will give all the house holds from the FactProspect table for month jan 2007.. than filter the factActive table based on this sub query.

For example: 100 Prospects in Jan 2007 out of which 30 become Active in March.

From SQL tables:
SELECT SUM(a.NumOfBookingThisMonth)
FROM dbo.FactHouseholdMonthActive a
JOIN dbo.DimHouseHoldType h
ON h.HouseHoldTypeSurKey = a.HouseHoldTypeSurKey
WHERE 1 = 1
AND a.MonthEndDateKey BETWEEN 20070228 AND 20070331
AND h.HouseHoldSubTypeName = 'Prospect Conversion'
AND EXISTS ( SELECT p.HouseHoldSurKey
FROM dbo.FactHouseholdMonthProspect p
WHERE p.MonthEndDateKey = 20070131
AND p.HouseHoldSurKey = a.HouseHoldSurKey
AND p.BrandSurkey = a.BrandSurkey )


We have tried to use exist function but we have had no success.

MDX Query :

with member [Measures].[1-2 MOnths] as
sum(
(
existing(
[Household].[House Hold Sur Key].[House Hold Sur Key].members
),
( [Date].[Dates].Currentmember.Lead(1):[Date].[Dates].Currentmember.lead(2),[HouseHoldType].[House Hold Sub Type Name].&[Prospect Conversion]
)
,[Measures].[Num Of Booking This Month]
))

select {[Measures].[1-2 MOnths]

} on columns
,[Date].[Dates].[Month].members on rows
from [MonthendStatus]
where ([Date].[Calendar Year].&[2007]:[Date].[Calendar Year].&[2014])

Thanks
Nachi