cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Patron
Post Patron

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
Post Patron
Post Patron

@Ashish_Mathur thank you!!

You are welcome.


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

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/

View solution in original post

Super User IV
Super User IV

@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])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Super User II
Super User II

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
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors