Select column from most recent month

Transact-SQL
1

I am not sure how to title this post but here is what I need to accomplish and I am having trouble figuring out how to pull it off.

I need to duplicate a report in either crystal or SSRS and I am trying to create the data source. The current report looks similar to the sample below. This is an operating expenses report and the amounts are in one table and the description in another. I need the user to select a month to run the report for and display the net change for that month (first amount) and the YTD balance. The issue I am having is if I select a month and there are no expenses (no net change) for that month how do I display the category and the YTD balance. How do I get that balance from the last row that exists for that category?

In addition I need to add a 3rd and 4th column to display the same amounts for the month the previous year.

I am just drawing a blank on how best to achieve this.

Here is a sampling of the rows:

rpt1
rpt1.jpg757×750 194 KB

and how they should be displayed:
rpt2

2

I think this is what you're looking for

Declare @t table (description varchar(50),
				  EOM Date,
				  MTD numeric(10,2),
				  YTD numeric(10,2))

declare @Date date = '6/30/2018'

insert into @t 
values
('Household & Clothing','4/30/2018',95.39, 5574.88),
('Household & Clothing','5/31/2018',95.39, 5670.27),
('Travel','5/31/2018',3316.74, 37690.52),
('Travel','6/30/2018',7553.86, 45244.38),
('Telephone','5/31/2018',8000, 88000),
('Telephone','6/30/2018',14705, 102705.04)

select v.Description, 
	   Case when v.EOM = @Date then 
			t.mtd
		else
			null
		end	MTD,
		t.YTD
  from @t t
	join (
select description, max(EOM) EOM 
  from @t
 where EOM <= @Date
 group by Description) v
	on t.description = v.description
	and t.EOM = v.EOM
order by 2