Identify ID changes

Hi serlurns,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.

Best regards,

Prasanna Kumar

Hi serlurns,

Thank you for reaching out to the Microsoft Fabric Forum Community.

To compare one month’s data with the previous month in Power BI and identify New IDs, Deactivated IDs, and Section Changes, you can use DAX to create a reference table that shifts each record by one month. Then, use LOOKUPVALUE to bring the previous month's section into the current table for comparison. You can create calculated columns to flag new IDs (if no match is found in the previous month), deactivated IDs (IDs present in the previous month but missing in the current), and section changes (where the section differs between months). These flags can then be used in a matrix visual to count and display changes by section and month.

use the DAX measures:

PreviousMonthData =
SELECTCOLUMNS(
ADDCOLUMNS(
'YourTable',
"NextMonth", [Month] + 1
),
"ID", [ID],
"Section_Prev", [Section],
"Month", [Month] + 1
)

ComparisonTable =
ADDCOLUMNS(
'YourTable',
"Section_Prev",
LOOKUPVALUE(
PreviousMonthData[Section_Prev],
PreviousMonthData[ID], 'YourTable'[ID],
PreviousMonthData[Month], 'YourTable'[Month]
)
)

NewID =
IF(
ISBLANK([Section_Prev]),
1,
0
)

DeactivatedIDs =
EXCEPT(
SELECTCOLUMNS(PreviousMonthData, "ID", [ID]),
SELECTCOLUMNS('YourTable', "ID", [ID]))

SectionChange =
IF(
NOT ISBLANK([Section_Prev]) &&
[Section_Prev] <> [Section],
1,
0
)

If you find this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to support others in the community.

Thank you & regards,
Prasanna Kumar