SQLTeam.com | Weblogs | Forums

SSRS Get latest value based on Max date in another column

I have two columns, Amount and Paid Date.

I need to retrieve the latest Amount based on the latest (or Max) Paid Date.

I have the expression below, it retrieves the latest Paid Date but retrieves the first Amount, not the latest amount.

=IIF(Fields!NoRentalUnits.Value > 0, LOOKUP(Fields!MaxPaidDate.Value, Fields!MaxPaidDate.Value, Fields!Amount.Value,"DataSet1"),"")

I also tried this:
=iif(Max(Fields!PaidDate.Value) = Fields!PaidDate.Value, Fields!Amount.Value,0)

I greatly appreciate assistance.


I would recommend you do this on the stored procedure that feeds this SSRS.

I was able to resolve by adding another DataSet to deal with the fees.