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.

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

shermayne123 , You need to use a common table and use that

 

Sumx(DimTable, NETWORKDAYS(Min('All FYs'[Manual PR Created Date]),Max('All FYs'[PR Approved Date]) ,2,VALUES('All FY Holidays'[Date]))

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

Hi amitchandak,

 

Maybe my initial message is not clear. I actually need to compute the net working days between Manual PR Created Date from the 'All FYs' table and Contract Signed Date from the 'Perf Metrics' table.  But using my original code for computing between two dates from the same table didn't work. 

 

By common table, do you mean I need to create a table that have these 2 fields (Manual PR Created Date and Contract Signed Date) ? Would you kindly advise how to go about doing that? And if these fields are already in the same table, won't my original code work then? Thanks in advance for clarifying.