• In this case I believe it would be easier and better to include logic in your fact table about whether or not the facility was closed at the time of the fact entry, and have a separate dimension to represent that.

    Doing this in MDX gets tricky because it doesn't really treat dimension data according to the raw data coming in. It treats it based on the hierarchies you define. There are some MDX functions that are specific to date dimensions and implement some of the logic, but its not so simple as saying give me dates greater than a certain one. In this case you need to tell it to give you a specific member and every member after it, plus one other member.

    That ought to be possible, but is pretty convoluted. And I don't think the mdx query should be doing the heavy lifting here. If pressed, I'd probably use LastPeriods and Prevmember to get all date members before your report date, then use except on a set of all the date members to get only the ones I wanted, and then add the default date in to the set. And assuming you didn't want to actually split up the measure by closing date, I would do all that in a calculated member within an aggregate. I think.

    As I said, the much cleaner solution is to have a Facility Status dimension (with open or closed) and have code in the view defining the fact table populating it.