Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
rafi_mridha
New Member

Problem facing while Comparing a Column based on second column

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!

 

1 REPLY 1
jgeddes
Super User
Super User

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

jgeddes_0-1712150538465.png

hope this gets you pointed in the right direction.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors