Forum Discussion

PowerBI123456's avatar
PowerBI123456
Post Partisan
5 years ago
Solved

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:  ...
Need Help
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Sorry, try this

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

TomMartens's avatar
TomMartens
Super User
5 years ago

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

PowerBI123456's avatar
PowerBI123456
Post Partisan
5 years ago

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