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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PowerBI123456
Post Partisan
Post Partisan

Getting closest date

Hi,

 

I have 2 tables below. I want to bring over the closest checked processed date after the check deposit date for each account. 

 

PowerBI123456_1-1606949809138.png

 

This is what the end results should look like: 

PowerBI123456_2-1606949830631.png

 

 

Thanks in advance!

1 ACCEPTED SOLUTION

Sorry, try this

=calculate(min('Table2'[Check Processed]),filter('Table2','Table2'[Account]=earlier('Table1'[Account])&&'Table2'[Check Processed]>earlier('Table1'[Check deposited])))


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

View solution in original post

8 REPLIES 8
PowerBI123456
Post Partisan
Post Partisan

@Ashish_Mathur thank you!!

You are welcome.


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

Hi,

Write this calculated column formula in Table1

=calculate(min('Table2'[Check Processed]),filter('Table2','Table2'[Account]=earlier('Table2'[Account])&&'Table2'[Check Processed]>earlier('Table1'[Check deposited])))

Hope this helps.


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

@Ashish_Mathur Thank you! But its not working for me, after the first earlier, its only letting me select from table 1. 

Sorry, try this

=calculate(min('Table2'[Check Processed]),filter('Table2','Table2'[Account]=earlier('Table1'[Account])&&'Table2'[Check Processed]>earlier('Table1'[Check deposited])))


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

@PowerBI123456 , Create a new column in table 1

 

New Column = minx(filter(table2, Table1[Account] = table2[account] && Table1[check Deposited] <=Table2[Check Processed]),Table2[Check Processed])

TomMartens
Super User
Super User

Hey @PowerBI123456 ,

 

can you please describe the business rule in more detail, as I do not fully understand the meaning of "closest" as for Account A the ABS(distance) to 2019-12-31 is one whereas the ABS(distance) to 2020-01-03 is 2.

 

Please describe the expected number of rows in both tables for the next five years, as this will determine the approach to tackle this challenge. 

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@TomMartens The check process date has to be after the check deposit date, it cannot be before. Hope that helps

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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