UPDATE t SET [AVE Monthly Sales] = x.avgsum FROM table t JOIN
(SELECT someuniquefield ,AVG(Units) OVER (ORDER BY Date DESC) avgsum FROM table
     WHERE date > DATEADD(m, -6, date - DATEPART(d, date) + 1) ) as x
ON x.someunqiuefield.=t.someuniquefield

CREATE TABLE #TestTable (Units int, Date_Field datetime, Master_ID int)
INSERT INTO #TestTable (Units, Date_Field, Master_ID)
VALUES
 (10,'2016-07-06',1)
,(20,'2016-08-06',1)
,(30,'2016-09-06',1)
,(40,'2016-10-06',1)
,(50,'2016-11-06',1)
,(60,'2016-12-06',1)
,(70,'2016-10-06',2)
,(80,'2016-11-06',2)
,(90,'2016-12-06',2)

SELECT
     tt.Master_ID
     ,tt.Date_Field
     ,tt.Units
     ,SUM(tt2.Units) Sum_Units
FROM #TestTable tt
LEFT JOIN #TestTable tt2
    ON tt2.Date_Field BETWEEN DATEADD(m,-3,tt.Date_Field) AND tt.Date_Field
    AND tt.Master_ID = tt2.Master_ID
GROUP BY tt.Master_ID, tt.Date_Field, tt.Units
ORDER BY tt.Master_ID, tt.Date_Field

Master_ID   Date_Field                  Units   Sum_Units
1           2016-07-06 00:00:00.000     10      10
1           2016-08-06 00:00:00.000     20      30
1           2016-09-06 00:00:00.000     30      60
1           2016-10-06 00:00:00.000     40      90
1           2016-11-06 00:00:00.000     50      120
1           2016-12-06 00:00:00.000     60      150
2           2016-10-06 00:00:00.000     70      70
2           2016-11-06 00:00:00.000     80      150
2           2016-12-06 00:00:00.000     90      240