Forum Discussion

shermayne123's avatar
shermayne123
Helper 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 a date in another table, it somehow couldn't detect that column and throws an error instead. Below is the script I used:

Networkingdays(Manual PR) = NETWORKDAYS('All FYs'[Manual PR Created Date],'All FYs'[PR Approved Date],2,VALUES('All FY Holidays'[Date]))
 
I merely changed the words in bold to 'Perf Metrics'[(i) Contract Signed Date] and the code breaks with an error stating that "A single value for column '(i) Contract Signed Date' in table 'Perf Metrics' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count and sum to get a single result."
 
I've tried to link both tables (All FYs and Perf Metrics) with PR No. but as both tables will not have unique PR No., I set up a many-to-many relationship. I suspect this could be the problem but not sure how to fix it. Hope someone can help, thank you so much!!
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.

18 Replies

  • amitchandak's avatar
    amitchandak
    Super 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
      Helper 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.

    • shermayne123's avatar
      shermayne123
      Helper I
      3 years ago

      Hi Padycosmos,

       

      Thanks for the video. 

       

      In this video, the order date and the ship date are from the same table. However, my dates are from two different tables. It doesn't really address how to compute net working days when the start and end date are from two different tables. Is there no way to obtain this except if these dates are from the same table?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super 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
      Helper 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
        Super User
        3 years ago

        Hi,

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

  • shermayne123's avatar
    shermayne123
    Helper I
    3 years ago

    Hi it's me again!

     
    I need to obtain the difference between two dates from two different tables but this time, I wasn't able to pull in the date into the same table using the same set of codes as advised in my previous query above. 

     

    My code is: 

    System PR Start Date = LOOKUPVALUE('System PR Table'[START_TIME],'System PR Table'[PR_REF_NO],'Perf Metrics'[PR_Ref?])
    The error message was: A table of multiple values was supplied when a single value was expected.
     
    Perf Metrics Table(relevant columns) 
    PR NoPR Item NoPR RefContract Signed Date
    PM220011PR-123455/5/2022
    PM230021PR-678904/6/2022
    PM210562PR-111217/7/2022
        
    System PR table  
    PR_REF_NOSTART_DATE  
    PR-123454/4/2022  
    PR-678904/5/2022  
    PR-111213/6/2022  

     

    I tried creating a new column in the System PR table to bring in the PR No. and PR Item No.(concatenated to be PRNo:PRItemNo) based on the PR Ref so that I can use it to bring in the Contract Signed Date from the second table(Perf Metrics) but it also failed. My code was:
    PRNOPRITEMNO = LOOKUPVALUE('Perf Metrics'[PRNO:PRITEMNO],'Perf Metrics'[PR_Ref?],'System PR '[PR_REF_NO])
    The error message is the same as the above.
     
    Hope someone can enlighten me on this, thank you so much!
     
     
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      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
        Helper I
        3 years ago

        Hi Ashish,

         

        Thanks for your enlightening advice.

         

        I've checked and found that there were indeed duplicates of PR_REF_NO as there were more than one START DATE for some of the PR_REF_NO. I further confirmed that we can use the latest date if this occurs. However, after sorting both the PR_REF_NO and the START DATE, I am at a loss how to extract only the correct date. I tried to use the conditional column method, but there seems to be no way to indicate this condition here.  Is it possible to do a if-else condition whereby if PR_REF_NO is duplicated, only extract the later date of whatever number of Start Dates available for each of this non-unique PR_REF_NO? 

         

        Thanks so much for helping with this!