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
Icon for Super User rankSuper User
3 years ago

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. 

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.