cancel
Showing results for
Did you mean:
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.

This is what the end results should look like:

1 ACCEPTED SOLUTION
Super User III

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

@Ashish_Mathur thank you!!

Super User III

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
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
Post Patron

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

Super User III

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

Proud to be a Super User!

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

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

Announcements

#### Happy New Year from Power BI

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