Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Sorry, try this
=calculate(min('Table2'[Check Processed]),filter('Table2','Table2'[Account]=earlier('Table1'[Account])&&'Table2'[Check Processed]>earlier('Table1'[Check deposited])))
You are welcome.
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])))
@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])
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |