Thanks Sean that worked. What about the below approach?

;with dates(Month_End) as (select cast('2014-01-01' as datetime)-1 as datetime union all select DATEADD(month, 1, Month_End+1)-1
from dates where Month_End < GETDATE()),Months As
(select DateAdd(month, -1, DateAdd(day, 1, Month_End)) As Month_Start,Month_End,ts.[Diary Reference],ts.[Tenant Code],ts.Category,ts.[Action Code],ts.[Action Date],ts.[Follow on Date],ts.[Created Date]
FROM @diary ts join dates d ON d.Month_End Between [Action Date] and DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,isnull(ts.[Follow on Date], GETDATE()))+1,0)))

SELECT * FROM (Select Month_End,months.[Tenant Code],MAX(months.[Action Date]) As [Max Action Date]
From Months Group BY Month_End,months.[Tenant Code]) AS Der order by Month_End, [Tenant Code]