Forum Discussion
Identify ID changes
Good afternoon,
I have a table with the fields Year, Month, ID, and Section.
I need to compare one month against another to identify:
New IDs. Example: ID 9976 appears in February but wasn’t in January.
IDs that are no longer present. Example: ID 4131 is no longer there in February.
Section changes for an ID. Example: ID 1234 switches from Sports to TV in February. -1 Sport +1 TV
I need to obtain the following three visualizations:
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
11 Replies
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 serlurns,
Thank you for reaching out to the Microsoft Fabric Forum Community.
Try this Dax measures.
New IDs =
VAR CurrentMonth = SELECTEDVALUE('Table'[Month])
RETURN
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[Month] = CurrentMonth &&
NOT 'Table'[ID] IN
CALCULATETABLE(
VALUES('Table'[ID]),
'Table'[Month] = CurrentMonth - 1
)
)
)
Deactivated IDs =
VAR CurrentMonth = SELECTEDVALUE('Table'[Month])
RETURN
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[Month] = CurrentMonth - 1 &&
NOT 'Table'[ID] IN
CALCULATETABLE(
VALUES('Table'[ID]),
'Table'[Month] = CurrentMonth
)
)
)Section Change =
VAR CurrentMonth = SELECTEDVALUE('Table'[Month])
VAR ChangedIDs =
FILTER(
ADDCOLUMNS(
VALUES('Table'[ID]),
"PrevSection", CALCULATE(MAX('Table'[Section]), 'Table'[Month] = CurrentMonth - 1),
"CurrSection", CALCULATE(MAX('Table'[Section]), 'Table'[Month] = CurrentMonth)
),
[PrevSection] <> [CurrSection]
&& NOT(ISBLANK([PrevSection]))
&& NOT(ISBLANK([CurrSection]))
)
VAR CurrentSection = SELECTEDVALUE('Table'[Section])
RETURN
SUMX(
ChangedIDs,
VAR PrevSec = [PrevSection]
VAR CurrSec = [CurrSection]
RETURN
SWITCH(
TRUE(),
CurrentSection = PrevSec && CurrentMonth > 1, -1,
CurrentSection = CurrSec, 1,
0
)
)If you found this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to assist others in the community.
Thank you and best regards,
Prasanna KumarHi 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
none of the measures give the expected result
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 KumarHi ,
We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.
If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution — it helps others who might face a similar issue.
Warm regards,
Prasanna Kumar
Hi serlurns,
We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.
If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution it helps others who might face a similar issue.
Warm regards,
Prasanna Kumar
Hi serlurns,
Just a gentle reminder has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.
This not only closes the loop on your query but also helps others in the community solve similar issues faster.
Thank you for your time and feedback!
Best,
Prasanna Kumar
