Forum Discussion
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
Create a measure to identify new IDs for each month:
NewIDs =
VAR CurrentMonth = SELECTEDVALUE('Table'[Month])
VAR PreviousMonth = CurrentMonth - 1
RETURN
COUNTROWS(
FILTER(
'Table',
'Table'[Month] = CurrentMonth &&
NOT 'Table'[ID] IN
SELECTCOLUMNS(
FILTER('Table', 'Table'[Month] = PreviousMonth),
"ID", 'Table'[ID]
)
)
)
Create a measure to identify IDs that are no longer present:
DeactivatedIDs =
VAR CurrentMonth = SELECTEDVALUE('Table'[Month])
VAR PreviousMonth = CurrentMonth - 1
RETURN
COUNTROWS(
FILTER(
'Table',
'Table'[Month] = PreviousMonth &&
NOT 'Table'[ID] IN
SELECTCOLUMNS(
FILTER('Table', 'Table'[Month] = CurrentMonth),
"ID", 'Table'[ID]
)
)
)
Create a measure to identify section changes for each ID:
SectionChanges =
VAR CurrentMonth = SELECTEDVALUE('Table'[Month])
VAR PreviousMonth = CurrentMonth - 1
RETURN
COUNTROWS(
FILTER(
'Table',
'Table'[Month] = CurrentMonth &&
'Table'[ID] IN
SELECTCOLUMNS(
FILTER('Table', 'Table'[Month] = PreviousMonth),
"ID", 'Table'[ID]
) &&
'Table'[Section] <>
LOOKUPVALUE('Table'[Section], 'Table'[ID], 'Table'[ID], 'Table'[Month], PreviousMonth)
)
)
Create a matrix visual.
Add Month to the columns.
Add Section to the rows.
Add the NewIDs measure to the values.
bhanu_gautam Thank you very much 😀😭. There is one special case that I don't know if it would work. That case is when comparing December 2024 vs. January 2025. Would it work with those measures?
serlurns To compare one month against another and identify new IDs, IDs that are no longer present, and section changes for an ID, you can use the following DAX measures in Power BI:
DAX
NewIDs =
VAR CurrentMonth = SELECTEDVALUE('Table'[Month])
VAR CurrentYear = SELECTEDVALUE('Table'[Year])
VAR PreviousMonth = IF(CurrentMonth = 1, 12, CurrentMonth - 1)
VAR PreviousYear = IF(CurrentMonth = 1, CurrentYear - 1, CurrentYear)
RETURN
COUNTROWS(
FILTER(
'Table',
'Table'[Month] = CurrentMonth &&
'Table'[Year] = CurrentYear &&
NOT 'Table'[ID] IN
SELECTCOLUMNS(
FILTER('Table', 'Table'[Month] = PreviousMonth && 'Table'[Year] = PreviousYear),
"ID", 'Table'[ID]
)
)
)Hi bhanu_gautam . I get the error that the syntax is not correct In PreviousMonth and in PreviousYear 😞
