Comparing List Additions and Removals Month to Month

Question

I've got a table from an SAP database that has part numbers and the months they were/are active.&nbsp;Date&nbsp; Part Number2023-04-01&nbsp; &nbsp; &nbsp;ABC12023-05-01&nbsp; &nbsp; &nbsp;ABC22023-05-01&nbsp; &nbsp; &nbsp;ABC32023-06-01&nbsp; &nbsp; &nbsp;ABC12023-06-01&nbsp; &nbsp; &nbsp;ABC2&nbsp;I want a final table that shows how many part numbers were added and/or removed for each month, from the previous month.&nbsp;DatePart Numbers Added&nbsp; &nbsp; &nbsp; &nbsp; Part Numbers Removed2023-04-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;null2023-05-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12023-06-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp;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:&nbsp;&nbsp;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])RETURNIF(ISBLANK(prev_rec), 1, 0)&nbsp;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:&nbsp;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])RETURNIF(ISBLANK(next_rec), 1, 0)&nbsp;&nbsp;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).&nbsp;DatePart Numbers Added&nbsp; &nbsp; &nbsp; Part Numbers Removed (expected)&nbsp; &nbsp; &nbsp; &nbsp;Part Numbers Removed (obtained)2023-04-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12023-05-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12023-06-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2

thxalot · Accepted Answer

MoM part.pbix&nbsp;A most common, fundamental use case of time intelligence function.

Forum Discussion

Comparing List Additions and Removals Month to Month

2 Replies

Related content

remove duplicate value within a row

Removing Duplicates

Visualization to depict additions, removals, and changes over time

SIlver Layer - Additional Columns

Remove trailing commas

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

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

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