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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Aydeedglz
Helper V
Helper V

Compare two dates from different tables, using if condition

I have two tables, Table 1 and Table 2, both are related based on account number.

 

I want to compare if date from table 1 = date from table 2, if is not the same then "different" is it is the same then "equal". How can I do an if statement, comparing both dates row by row so I can add it to table.

7 REPLIES 7
jgeddes
Super User
Super User

You may be able to use the LOOKUPVALUE function in this case. 
As an example I have two tables
Table 1

Date Account
3/3/2024 ABC
3/3/2024 DEF
3/4/2024 ABC
3/5/2024 GHI
3/5/2024 DEF

and Table 2

Date Account
3/3/2024 ABC
3/4/2024 DEF
3/5/2024 GHI

 

and I want to know if the values in Table 1 appear in Table 2. 
I can add a calculated column to Table 1 with the following code...

isOnTable2 = 
var _lookup = 
LOOKUPVALUE('Table 2'[Account], 'Table 2'[Account], [Account], 'Table 2'[Date], [Date])
Return
IF(
    _lookup = [Account],
    "Yes",
    "No"
)

and end up with the result...

jgeddes_0-1711569087018.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!





Hi, is not working, see the following example I am getting  

Aydeedglz_0-1711571845141.png

 

If you can provide a sample of your data (nothing sensitive) I will likely be able to provide a better solution for you.




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

Proud to be a Super User!





I can't share the data

 

Table 1:

Account & Date

Table 2:
Account & Date

Relationship: Account & Account

The column that I am creating is in the Table 1, and there are more columns. I created a table with more columns ej. Name, Second Name, Account, Date table 1, date table 2

Hi,

Share some dummy data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

TABLE 1  TABLE 2  RESULT
AccountDate AccountDate Result
765A1/1/2022 765A11/12/2023 FALSE
987D5/7/2023 987D4/5/2023 FALSE
036G9/2/2023 036G9/2/2023 TRUE
872B9/9/2023 872B9/9/2023 TRUE
926K4/5/2023 926K1/1/2022 FALSE
047A6/1/2023 047A6/1/2023 TRUE
102M2/3/2023 102M2/3/2023 TRUE
094K7/8/2022 094K9/9/2023 FALSE
237V5/7/2023 237V12/5/2023 FALSE

Hi,

In Table1, write this calculated column formula

Column = if(CALCULATE(min(Table2[Date]),FILTER(Table2,Table2[Account]=EARLIER(Table1[Account])))=Table1[Date],"Same","Different")

Hope this helps.

Ashish_Mathur_0-1711670061833.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.