Forum Discussion
Calculate Net Working Days from dates in two different tables
Hi,
This calculated column formula in the ALL FYs table works
Column = LOOKUPVALUE('Perf metrics'[Contract Signed Date],'Perf metrics'[(PR) PR No.],'all Fys'[(PR) PR No.],'Perf metrics'[(PR) PR Item No.],'all Fys'[(PR) PR Item No.])Hope this helps.
Hi,
That error message means that there are duplicate entries appearing in the PR_REF_NO column of the System PR table.
Hi Ashish,
While waiting I thought of a method i.e. to delete the duplicate records with the earlier start dates , by sorting the PR_REF_NO in ascending order and START DATE in descending order, assuming the software will delete the second record which has the earlier date, but it deleted the later date instead. When I resort it by ascending order, it will delete the second record with the later date and keep the first one with the earlier date. So this method doesn't work.
Below is my table and the expected result in column "Selected Start Date":
| System PR table | ||
| PR_REF_NO | START_DATE | Selected Start Date |
| PR-12345 | 4/4/2022 | record to be deleted |
| PR-12345 | 5/5/2022 | 5/5/2022 |
| PR-67890 | 3/3/2022 | record to be deleted |
| PR-67890 | 4/5/2022 | 4/5/2022 |
| PR-11121 | 3/6/2022 | 3/6/2022 |
| PR-14589 | 6/6/2022 | 6/6/2022 |
Hope you can help, thanks!!
Hi Ashish,
Good news is I managed to use GroupBy method to get the most recent date/selected start date column populated by PR_REF_NO. Then I tried to lookup the value of this date into the Perf Metrics table as before using the same code:
Hi Ashish,
I further checked with my colleague and confirmed that the problem it is possible for Perf Metrics table not to have all the system PRs due to timing issue. As such, I'll close this query, thanks so much!!