SSRS Expression to return previous month year value

Question

I'm trying to come up with an SSRS expression to return a previous value from the year prior. As you can see in the screenshot, I'm trying to return the value of 22 in the "Prior Year Actual" column. This value aligns with April 2020. The last month I have in the dataset currently is April 2021. Essentially I want to return the value from the year prior based on the last column of April 2021.

Can anyone help with this? Greatly appreciated!

Answer 1

If your month fields are dates in a column group, you could get the MAX date and add it to the dataset.

DECLARE @MAX_DATE DATE = (SELECT MAX(DATE_FIELD) FROM TABLE)

SELECT *, @MAX_DATE AS MAX_DATE 
FROM TABLE 

Then your expression could use the new field to make it work like you want.

=MAX(IIF(Fields!Date.Value = DATEADD("y", -1, Fields!MAX_DATE.Value), Fields!COLUMN.Value, NOTHING))