Retrieve value based on Max date where max date is <= Prompt Date

[rsfall]

Need to retrieve a value based on the newest date (max).   The max date has to be less than or equal to Prompt Date.  I can get the max date and the value associated with it, but can't seem to get the max date less than or equal to prompt date condition to work. In the example below , am getting value 30, instead of 10.
Syntax to accomplish this within a report studio query / filter would be much appreciated.

Date          value     Prompt Date
----------    ------     -------------
01/01/15    10         01/15/15
02/01/15    20
03/01/15    30

[MFGF]

Need to retrieve a value based on the newest date (max).   The max date has to be less than or equal to Prompt Date.  I can get the max date and the value associated with it, but can't seem to get the max date less than or equal to prompt date condition to work. In the example below , am getting value 30, instead of 10.
Syntax to accomplish this within a report studio query / filter would be much appreciated.

Date          value     Prompt Date
----------    ------     -------------
01/01/15    10         01/15/15
02/01/15    20
03/01/15    30

Hi,

I do have an approach you might try, but it's not very efficient.

1. Bring in the date and sort it descending.
2. Add a query calculation using running-count() summary based on the date item - ie running-count([your date item])
3. Filter your report based on your date parameter from the prompt - ie [your date item] <= ?Your date param? - set the timing of this to "Before auto aggregation"
4. Filter your report based on the running count - ie [your running count calculation] = 1 - set the timing of this to "After auto aggregation"

Cheers!

MF.

[bdbits]

Relational or dimensional? If relational, what database? If I understand you correctly, there are expressions to get you what you want, but the data source matters.

[rsfall]

It's relational.   Database is Oracle.