DAX formula for calculate Sum between 2 dates

daxpowerpivot

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.

Best Answer

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.

Related Topic