Forum Discussion

PowerBI123456's avatar
PowerBI123456
Icon for Post Partisan rankPost 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
Icon for Super User rankSuper 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
    Icon for Post Partisan rankPost Partisan
    5 years ago

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