Forum Discussion
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:
Thanks in advance!
Sorry, try this
=calculate(min('Table2'[Check Processed]),filter('Table2','Table2'[Account]=earlier('Table1'[Account])&&'Table2'[Check Processed]>earlier('Table1'[Check deposited])))
8 Replies
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])
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.
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])))
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
TomMartens The check process date has to be after the check deposit date, it cannot be before. Hope that helps
Ashish_Mathur thank you!!
You are welcome.
