reporting services - Get previous first value from parameter

Question

I have a parameter date from dataset date: Sample of data

Key	Month	Year	Row num
20210131	January	2021	3
20201231	December	2020	2
20201130	November	2020	1

question from:https://stackoverflow.com/questions/66049447/get-previous-first-value-from-parameter

Answer 1

I would suggest that you extend the dataset query to include the previous value against each row.

Assuming you dataset query is called DataSet1 and uses a table called 'myTable' then something like this (I changed the sample column name from Key to KeyID to avoid keyword problems).

    SELECT *
        , LAG(KeyID) OVER(ORDER BY RowNum) as PreviousMonthKeyID
        FROM myTable

NOTE: This assumes row number is always in the correct order, if not then you could change this to order by KeyID.

You can then use a LOOKUP() to find the correct value, so if your parameter is called selectedDate and it's value is set to the KeyID column, use something like

=LOOKUP(Parameters!selectDate.Value, Fields!KeyID.Value, Fields!PreviousMonthKey.Value, "DataSet1")

This reads "get the value in selectdDate, find this value in the column KeyID in the dataset called DataSet1 and return the value found in the column called PreviousMonthKeyID"