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 ...
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
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!

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        3 years ago

        Hi,

        Share some data and show the expected result.