DAX formula for calculate Sum between 2 dates

Question

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 the xlsx file.

Answer 1

I couldn't download the file but what you are after is what Rob Collie calls the 'Greatest Formula in the World' (GFITW). This is untested but try:

= CALCULATE (
SUM ( WKRelStrength[RS+-] ),
FILTER (
    ALL ( Cal ),
    Cal[Date] <= MAX ( Cal[Date] )
        && Cal[Date]
            >= MAX ( Cal[Date] ) - 90
) )

Note, this will give you the previous 90 days which is approx 3 months, getting exactly the prior 3 calendar months may be possible but arguably is less optimal as you are going to be comparing slightly different lengths of time (personal choice I guess).

Also, this will behave 'strangely' if you have a total in that it will use the last date in your selection.

Answer 2

First of all, the formula that you are using is designed to work as a Measure. This may not work well for a Calculated Column. Secondly, it is better to do such aggregations as a Measure level, than at individual records.

Then again, I do not fully understand your situation, but if it is absolutely important for you to do this at a Record level, you may want to use the "Earlier" Function.

If you want to filter a function, based on a value in the correspontinf row, you just have to wrap your Column name with the Earlier Function. Try changing the LastDate to Earlier in your formula.