I was wondering how I might do this:

We have a Point of Sale data warehouse. I'd like to build a cube with a
dimension that represents "Last 4 Weeks", "Last 13 Weeks" and "Last 52
Weeks", all based on today's (the time when the user is using the pivot
table, etc) date.

Is there anyway to build a dimension that will also have the actual week
ending dates underneath (but calculated on the fly)

Here's how I'd like it to work:

Within Excel (pivot table) I have a Dimension called "Periods" that looks
like this:

Periods
Current Week
Last 4 Weeks
Last 13 Weeks
Last 52 Weeks

The user would be able to drag the dimension over to the pivot table and it
would look like this (for a user viewing the data sales week 200508):

Unit Sales
Last 4 Weeks 200508 10000
200507 10023
200506 30133
200505 30433

But the following week, the data would be:
Unit Sales
Last 4 Weeks 200509 13023
200508 10000
200507 10023
200506 30133

I would also like to be able to do distinct counts, for example unique
stores that sold a UPC, etc.

Thanks in advance-
Malcolm