Comparing List Additions and Removals Month to Month

[calcstuff]

I've got a table from an SAP database that has part numbers and the months they were/are active.

Date	Part Number
2023-04-01	ABC1
2023-05-01	ABC2
2023-05-01	ABC3
2023-06-01	ABC1
2023-06-01	ABC2

I want a final table that shows how many part numbers were added and/or removed for each month, from the previous month.

Date	Part Numbers Added	Part Numbers Removed
2023-04-01	1	null
2023-05-01	2	1
2023-06-01	1	1

I can create a table visualization to obtain the table above, and the new "Part Numbers Added" column is simply a sum from the following DAX: 

Part Numbers Added = 
VAR prev_rec =
LOOKUPVALUE(
'sap'[Part Number],
'sap'[Date], 
DATE(YEAR('sap'[Date]), MONTH('sap'[Date]) - 1, 1), 
'sap'[Part Number], 
'sap'[Part Number]
)
RETURN
IF(ISBLANK(prev_rec), 
1, 
0
)

However, when I try and calculate the "Part Numbers Removed" (comparing to the previous month) I get the right values, but they lead in months by +1. Here's my DAX:

Part Numbers Removed = 
VAR next_rec =
LOOKUPVALUE(
'sap'[Part Number],
'sap'[Date], 
DATE(YEAR('sap'[Date]), MONTH('sap'[Date]) + 1, 1),
'sap'[Part Number], 
'sap'[Part Number]
)
RETURN
IF(ISBLANK(next_rec), 
1, 
0
)

 How do I adjust the logic to get part numbers removed (from last month, compared to this month)? Currently I get this, which is the part numbers removed from next month (compared to this month).

Date	Part Numbers Added	Part Numbers Removed (expected)	Part Numbers Removed (obtained)
2023-04-01	1	null	1
2023-05-01	2	1	1
2023-06-01	1	1	2

[tamerj1]

Hi @calcstuff 
Please refer to attached sample file with the proposed solution

Part Numbers Added = 
VAR Current_Month = SELECTEDVALUE ( SAP[Date] )
VAR Previous_Month = EOMONTH ( Current_Month, - 2 ) + 1
VAR CurrentPartNumbers = VALUES ( SAP[  Part Number] )
VAR PreviousPartNumbers = 
    CALCULATETABLE (
        VALUES ( SAP[  Part Number] ), 
        SAP[Date] = Previous_Month 
    ) 
VAR NewPartNumbers = EXCEPT ( CurrentPartNumbers, PreviousPartNumbers )
RETURN
    COUNTROWS ( NewPartNumbers )

Part Numbers Removed = 
VAR Current_Month = SELECTEDVALUE ( SAP[Date] )
VAR Previous_Month = EOMONTH ( Current_Month, - 2 ) + 1
VAR CurrentPartNumbers = VALUES ( SAP[  Part Number] )
VAR PreviousPartNumbers = 
    CALCULATETABLE (
        VALUES ( SAP[  Part Number] ), 
        SAP[Date] = Previous_Month 
    )
VAR RemovedPartNumbers = EXCEPT ( PreviousPartNumbers, CurrentPartNumbers )
RETURN
    COUNTROWS ( RemovedPartNumbers )

[ThxAlot · Accepted solution]

MoM part.pbix

A most common, fundamental use case of time intelligence function.