Using Date Variables in Set Analysis

[#1]

I have a set analysis expression with two variable, Date, and Forecast.

=sum({<Date={'7/31/2011'}, Forecast={'Actual'}>}AvgBalance)

I am trying to use a more flexible expression for the Date variable.  Essentiall I want the date to be the most recent with an Actual value.

I have tried using a sql select statement to create a new variable named CurrentDate in the load:

sql select MAX(date) as CurrentDate from graph_data where Forecast = 'actual';

This works fine and the value of CurrentDate is correct.  But when I try to add this new variable to the set analysis, it doesn't work:

=sum({<Date=CurrentDate, Forecast={'Actual'}>}AvgBalance

Is this possible?

I have also tried to get a date based off of today() function.  Using Date(MonthEnd(Today(), -1)'MM/DD/YYYY') I can a date I can live with.

However if I substitute this expression into the set analysis for '7/31/2011' it will not work.

=sum({Date={'(MonthEnd(Today(),-1)'MM/DD/YYYY')}, Forecast={'Actual'}>}AvgBalance

In reading past posts this may not be working due to MonthEnd() adding time onto the value.

If I try a if(CurrentDate = Date, 'True', 'False') or substitute the expression above for CurrentDate I do get a True value for Date 7/31/2011, so they do equal in this expression.

Any Advice?

[#2]

I can't seem to get the Today function to work inside a Set statement.

I have the following statement, which works fine if I hard code an actual date in it, but I want to show a rolling 10 weeks of data in my chart.

=avg({$<[MI Date]={'>=today()-70'}>}[DS_TotalScore])

If anyone can tell me where I'm going wrong, I would greatly appreciate it!

[GabrielAraya]

Try
={">=$(=Today())"} >}

Gabriel

[#4]

Thanks Gabriel...I can get there to work, but it doesn't work when I put in -70 in the forumla to look back 70 days.

Here's what I have now:

=avg({$<[MI Date]={">=$(=Today()-70)"} >}[DS_TotalScore])

Do you see anything wrong?