Forum Discussion

shermayne123's avatar
shermayne123
Icon for Helper I rankHelper I
3 years ago
Solved

Calculate Net Working Days from dates in two different tables

Hi,    I have previously used the following measure to calculate the net working days from 2 dates in the same table and was able to get the days correctly. But when I switched one of the dates to ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    That error message means that there are duplicate entries appearing in the PR_REF_NO column of the System PR table.

shermayne123's avatar
shermayne123
Icon for Helper I rankHelper I
3 years ago

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!

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
3 years ago

Hi,

I can help further if you share some data and show the expected result.

  • shermayne123's avatar
    shermayne123
    Icon for Helper I rankHelper I
    3 years ago

    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
    ABC100013/1/2023
    DEF200013/2/2023
    DEF200023/3/2023
    DEF20003

    3/4/2023

     

    Perf Metrics Table

    (PR) PR No.(PR) PR Item No.Contract Signed Date
    ABC100013/14/2023
    DEF200013/10/2023
    DEF200023/10/2023
    DEF200033/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!!

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      3 years ago

      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.

      • shermayne123's avatar
        shermayne123
        Icon for Helper I rankHelper I
        3 years ago

        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!