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,
Due to security issue, I won't be able to share much actual data but here's a simplified example:
All FYs table:
| (PR) PR No. | (PR) PR Item No. | Manual PR Created Date |
| ABC1000 | 1 | 3/1/2023 |
| DEF2000 | 1 | 3/2/2023 |
| DEF2000 | 2 | 3/3/2023 |
| DEF2000 | 3 | 3/4/2023 |
Perf Metrics Table
| (PR) PR No. | (PR) PR Item No. | Contract Signed Date |
| ABC1000 | 1 | 3/14/2023 |
| DEF2000 | 1 | 3/10/2023 |
| DEF2000 | 2 | 3/10/2023 |
| DEF2000 | 3 | 3/8/2023 |
So what is required is to compute the net working days between Manual PR Created Date and Contract Signed Date excluding weekends(Sat&Sun) and public holidays. Hope that is clear, otherwise feel free to let me know. Thanks so much!!
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 Ashish,
Thanks so much for your screenshot! I managed to add in this new column to pull in the "Contract Signed Date" with your formula and was able to compute the net working days based on my original formula now. So the solution is to have the second date column added into the first table before applying my formula. Thank you!
You are welcome.