DAX formula for calculate Sum between 2 dates not working....

[hlask]

Hi,

I have a couple of tables in PowerPivot:
1. A Stock table - WKRelStrength whose fields are:
Ticker, Date, StockvsMarket% (values are percentages), RS+- (values can be 0 or 1)

2. A Calendar Table - Cal with a Date field.

There is a many to one relationship between the tables.

I am trying to aggregate RS+-against each row for dates between 3 months ago to the date for that row - i.e a 3 month to date sum. I have tried numerous calculations but the best I can return is an circular reference error. Here is my formula:

=calculate(sum([RS+-]),DATESINPERIOD(Cal[Date],LASTDATE(Cal[Date]),-3,Month))

Here is a link to the file: http://1drv.ms/QRPIln

Thanks.....

 

[theBardd]

I'm getting an error that the file exceeds 5Mb, can you reduce it in size a bit?

 

[hlask]

Thanks - I have created a 'lite' version by reducing the number of rows. Here is the link:http://1drv.ms/1m5AVRZ

 

[Tianbas]

maybe not the best solution but this formula as calculated column in the stock table should deliver the result

=calculate(sum(Table1[RS]),filter(Table1,and(Table1[Date]<(earlier(Table1[Date])+1),Table1[Date]>(earlier(Table1[Date])-30))))

there is also something like datesinbetween but I haven't used that so far