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,
In the ALL FY table, write a calculated column formula to bring over the Date fields from the Other table. Then write your formula as a calculated column formula (not as a measure).
Hope this helps.
Hi Ashish,
Thanks for your reply.
Do you mean to add in this column "Contract Signed Date" in the All FYs table through a calculation? Or bring it over by using a calculation that involves this date column? For the former scenario, I don't think there is a fixed schedule of when this date happens for every PR so can't calculate it per se. For the latter, I was already using a calculated column formula, not a measure. Hope you enlighten further how to go about resolving this issue. Thanks!
Hi,
I can help further if you share some data and show the expected result.
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!!
