i believe that this your answer is
CREATE TABLE #test (EmpName NVARCHAR(50), Manager NVARCHAR(50), Empsales INT, [Month] int);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('A', 'X', 100,1);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('E' , 'X' , 200 ,1);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('F' , 'X' , 150 ,1);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('B' , 'Y' , 100 ,1);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('C' , 'Y' , 200 ,1);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('D' , 'Z' , 150 ,1);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('G' , 'Z' , 100 ,1);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('A' , 'X' , 150 ,2);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('E' , 'X' ,250 ,2);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('F' ,'X', 200 ,2);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('B', 'Y' , 100 ,2);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('C' , 'Y' , 250,2);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('D' , 'Z' , 200 ,2);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('G' , 'Z' , 100 ,2);
declare @ToMonth int=2;
declare @FromMonth int=1;
SELECT a.EmpName, a.Manager , a.[Month], SUM(Empsales) Empsales, TeamSales, MTDsales FROM #test a JOIN (SELECT Manager, [Month], SUM(Empsales) TeamSales FROM #test where [Month]=@tomonth group by Manager, [Month]) TeamSales on a.Manager=TeamSales.Manager and a.[Month]=TeamSales.[Month]
JOIN (SELECT Manager, Max([Month]) [Month], SUM(Empsales) MTDsales FROM #test where [Month]>=@FromMonth and [Month]<=@ToMonth group by Manager) MTDsales on a.Manager=MTDsales.Manager and a.[Month]=MTDsales.[Month]
group by a.EmpName, a.Manager , a.[Month], TeamSales, MTDsales
DROP TABLE #test
output
EmpName Manager Month Empsales TeamSales MTDsales
A X 2 150 600 1050
B Y 2 100 350 650
C Y 2 250 350 650
D Z 2 200 300 550
E X 2 250 600 1050
F X 2 200 600 1050
G Z 2 100 300 550