Default Member - Year Month

[dtufte]

I need some help determining the best way to have DefaultMember date values in an SSAS 2008 cube. The business users want to be able to have their report starting points automatically have current (accounting) year, and current (accounting) month for Actual vs Budget, Actual Prior Month vs Current month, Prior Year, etc.

The catch is that they have a small, 4 row table that has the dates of Current month, and Closed month (two different reporting closing month values) and a row that this application does not need. As an example, the current Month-end value is 200904, and the current date value is 200905. They have some Relational reports that currently lookup these values, and run based on these dates. This table also has the date when the data was loaded, and they need to be able to have this date loaded on the report (i.e. "Data current as of 2009-05-04") as a footer.

There are two main types of reports - operational that want numbers as of today, and financial the want to see the month end numbers.

I have looked at the ClosingPeriod, but I think that means the last member that has data - not necessarily the month-end month.

So what are my best options? Can I create a calculated member that can look up the value from the "Current Date" table? Can I build the defaultmember to look this up?

I would appreciate any help,

Thank you.

Dave

[Island1]

Hi:

Assuming the data in the fact table / transaction table has a date assigned to each row - a date that can be used for your purposes - have you considered using MDX time functions within calculated members to accomplish this?

Thanks.

Bill