SSRS Sum Values Based on Earliest Date

Question

I'm trying to sum a net balance based on the earliest date in an SSRS report. In this case there are only 2 dates, but there can be more dates not more than 7 days.

Here's a sample of my data:

Here's what I'm trying to get with the earliest date of 10/26/15:

I've tried the following code, but not able to get this to work:

    =Sum(IIf(DateDiff("d",Fields!SettleFullDate.Value,today())>=7 
and DateDiff("d", Fields!SettleFullDate.Value, today())<7
and Fields!SETTLEBALANCE.Value>0), Fields!SETTLEBALANCE.Value, 0)

Update: I tried the code below and keep getting an error on the report. Could it be that I need to change the date field to an integer?

Thanks in advance for your help!

Answer 1

To compare the sum of values of two dates, the maximum and minimum in a set you can use the following equation

=Sum(iif(Fields!myDate.Value = Max(Fields!myDate.Value), Fields!myVal.Value, 0))
-Sum(iif(Fields!myDate.Value = MIN(Fields!myDate.Value), Fields!myVal.Value, 0))

This Sums all the values that match the maximum date in the dataset together, and sums all the values that match the minimum date in the dataset together, and takes one from the other.

It is irrespective of which dates you ask to be received, the above approach will work only against the records that you return to SSRS. So if you have a filter (WHERE clause) to return records between Date1 and Date2 this will still apply (Note - don't actually use 'Between' in the query)

Rather than using the maximum and minimum dates as listed here, you could also calculate a date similar to your original approach using

dateadd("d", -7, Fields!MySpecificDate.Value)

And insert that to the expression above.

Hopefully this is what you require - if not please let me know.