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.

      • shermayne123's avatar
        shermayne123
        Helper I
        3 years ago

        Hi Ashish,

         

        While waiting I thought of a method i.e. to delete the duplicate records with the earlier start dates , by sorting the PR_REF_NO in ascending order and START DATE in descending order, assuming the software will delete the second record which has the earlier date, but it deleted the later date instead. When I resort it by ascending order, it will delete the second record with the later date and keep the first one with the earlier date. So this method doesn't work. 

         

        Below is my table and the expected result in column "Selected Start Date":

         

        System PR table 
        PR_REF_NOSTART_DATESelected Start Date
        PR-123454/4/2022record to be deleted
        PR-123455/5/20225/5/2022
        PR-678903/3/2022record to be deleted
        PR-678904/5/20224/5/2022
        PR-111213/6/20223/6/2022
        PR-145896/6/20226/6/2022

         

        Hope you can help, thanks!!