Date range + parallel time

Hi,

I'm quite new to MDX and found a problem I can not solve. I have a fact table which contains a start date and an end date, plus several measures. I also have a time dimension, with years and months. I need to dynamically build an mdx query that, given a year and a month, would show any measure in the fact table whose start date is lower than the given date and the end date is higher than the given date. To complicate things a bit, the same query must show the same measure in the previous year to the given date. Both results must be in the same axis.

We know how to show each result separately, using date ranges in the WHERE clause, but have no idea about how to combine both results.

Thanks

--eduardj

Finally solved it.

I created a date range dimension, with all the date ranges in the original fact table, and a measure-less fact table with a relationship with the new date range dimension and the old time dimension. After that, I created a Many-to-Many relationship between the original fact table and the time dimension through the newly created fact table. Besides, I created a hierarchy in the time dimension which related the times (parallel) that always had to be shown together.

Kind of messy, but it works beautifully.

thanks

--eduardj