I prefer to use pivot for this type of operation.
DECLARE @tbl TABLE (Total2 int NOT NULL,
Member_Month date NOT NULL,
New_Enrolled_Status varchar(200) NOT NULL,
Product_Type varchar(40) NOT NULL
)
INSERT @tbl(Total2, Member_Month, New_Enrolled_Status, Product_Type)
VALUES
(335, '20220101', 'Beginning Members', 'A'),
(339, '20211201', 'Total included members', 'A'),
(337, '20211201', 'Beginning Members', 'A'),
(13, '20211201', 'Disenrolled', 'A'),
(15, '20211201', 'New Adds', 'A'),
(35, '20220101', 'Beginning Members', 'B'),
(39, '20211201', 'Total included members', 'B'),
(37, '20211201', 'Beginning Members', 'B'),
(3, '20211201', 'Disenrolled', 'B'),
(5, '20211201', 'New Adds', 'B')
;with prevtotals as (
SELECT
Product_Type,
Member_Month,
[Beginning Members],
LAG([Total included members], 1,0) OVER (PARTITION BY Product_Type ORDER BY Member_Month) as [Prev_Total included members],
LAG([Beginning Members], 1,0) OVER (PARTITION BY Product_Type ORDER BY Member_Month) as [Prev_Beginning Members],
LAG([Disenrolled], 1,0) OVER (PARTITION BY Product_Type ORDER BY Member_Month) as [Prev_Disenrolled],
LAG([New Adds], 1,0) OVER (PARTITION BY Product_Type ORDER BY Member_Month) as [Prev_New Adds]
FROM (
SELECT
Product_Type, Member_Month, New_Enrolled_Status, Total2
FROM @tbl
) as src
PIVOT
(
MAX(Total2) FOR New_Enrolled_Status IN ([Beginning Members],[Disenrolled],[New Adds],[Total included members])
) as pvt
)
SELECT
[Beginning Members]-([Prev_Total included members]+[Prev_New Adds]-[Prev_Disenrolled]) as [Discrepancy_Value],
[Product_Type],
[Member_Month]
FROM prevtotals
ORDER BY Product_Type, Member_Month DESC