how to get ytd last year up to the same months this year in ssrs

Question

How to get the last year ytd vs the same months current year ytd in ssrs. Example, If my current year ytd is from Jan to March 2022, my last year ytd should be from Jan to March 2021 only but I'm getting all the months from my last year ytd as shown in the attached screen shot. I have tried my best to really figure this out and read a lot of topics about this but nothing helps me. The below is the query I used to arrive my current year vs last year.

SELECT
  Sum(Amount) as GrossAmt
  ,Year(tranDate) as Year
  ,Month(tranDate) as Month
  ,SUM(CASE when YEAR(tranDate) = @Year -1 THEN Amount END) AS LYAmt
  ,SUM(CASE WHEN YEAR(tranDate) = @Year THEN Amount END) AS ThisYAmt
FROM
  ( )
WHERE 
   Year(tranDate) BETWEEN @Year -1 and @Year

Thanks in advance to those who generously wants to help.

Answer 1

This assumes you only want to see the same months in both columns.

There are a few ways of doing this but a simple method that should give reasonable performance would be to simply put your results into a temp table then only return records where the months match in both years

something like....

SELECT
  Sum(Amount) as GrossAmt
  ,Year(tranDate) as Year
  ,Month(tranDate) as Month
  ,SUM(CASE when YEAR(tranDate) = @Year -1 THEN Amount END) AS LYAmt
  ,SUM(CASE WHEN YEAR(tranDate) = @Year THEN Amount END) AS ThisYAmt
INTO #temp
FROM
  ( )
WHERE 
   Year(tranDate) BETWEEN @Year -1 and @Year

SELECT 
    a.*
FROM #temp a
    JOIN (SELECT DISTINCT [Month] FROM #temp WHERE [Year] = @Year) b

This will simply get a list of months for the current year (1 -3 in your exmaple) and filter the output to just those months.