Find Last Value as at Report To Date

[rbudgeon]

Hi all. I'm creating a report where I need to bring in the last charge amount.

An example of my data is:

RentStartDate              RentAmt

01/01/2020                    300.00

01/09/2020                    360.00

01/10/2020                    260.00

01/11/2020                    270.00

At present I'm using:

Sum({<selectedToDate = {'$(=max(RentStartDate))'}>} RentAmt)

Where selectedToDate is a date variable that a user can select on the report.

Where RentStartDate is the start date of the rent record and RentAmt is the rent charge amount that I want to bring in.

I've selected 02/10/2020 on my report which I would expect to return 260.00, however it is bringing through 1190 which is the total of all of the RentAmts.

Any help would be appreciated.

[Vegar]

You can not make a modifier for a variable, you can only do it for fields. Consider set analysis as a way to create invisible selection in your data model in order to get a correct calculation. 

I'll try to rewrite you expression as I understood you, correct me if you think that I missunderstood you. 

Sum({<RentStartDate={'$(=maxstring({<RentStartDate={" <=' $(selectedToDate)'} ">} RentStartDate))' } RentAmt)