Problem facing while Comparing a Column based on second column

[Anonymous]

Hello!!! I am new to Power Bi and need a help!

I have two excel sheets: Column A consists of the documents numbers and Column B consists of date.
The Previous month and the Current month stored in separate Excel sheets, each containing document numbers generated by our employees.

The challenge is to compare these two sets of data to identify any changes, including new documents added in the current month and documents that were present in the previous month but are no longer present in the current month.

I created a custom column named "Month name" such as previous month to indetify before I combined both tables. After that, taking help from ChatGPT I wrote this formula which didnt work. It shows: "Expression error: A cyclic reference was encountered during evaluation."

= if [Identifier] = "Previous Month" and not List.Contains(PreviousMonthTable[Unique Identifier], [Unique Identifier]) then "New Document" else "Not New Document"

Can anyone assist me in solving this? I would really appreciate that. Thank you!

[jgeddes · Accepted solution]

How your data is organized will dictate which solution would work best for you. If you can append your two sheets together into one table you could then group the appended table by document id, aggregating the max value of the date column. From there you could add a custom column that tests if the month of the max date value for a document id is equal to the month value of the current month. It would look something like...
marchTable

DocumentDate

12345	3/1/2024
26456	3/1/2024
65972	3/3/2024
99543	3/17/2024

aprilTable

DocumentDate

12345	4/1/2024
55348	4/1/2024
65972	4/3/2024
55671	4/3/2024

let
    Source = 
    Table.Combine({marchTable, aprilTable}),
    #"Grouped Rows" = 
    Table.Group(Source, {"Document"}, {{"_maxDate", each List.Max([Date]), type nullable date}}),
    #"Added Custom" = 
    Table.AddColumn(#"Grouped Rows", "isCurrentMonth", each if Date.Month([_maxDate]) = Date.Month(DateTime.FixedLocalNow()) then "New Document" else "Not New Document", type text)
in
    #"Added Custom"

to end up with

hope this gets you pointed in the right direction.