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



## hlask (Apr 28, 2014)

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 (Apr 28, 2014)

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


----------



## hlask (Apr 29, 2014)

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


----------



## Tianbas (Apr 29, 2014)

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


----------

