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 Ashish,

 

Good news is I managed to use GroupBy method to get the most recent date/selected start date column populated by PR_REF_NO.  Then I tried to lookup the value of this date into the Perf Metrics table as before using the same code:

 

System PR Start Date = LOOKUPVALUE('System PR Report'[Most Recent Start Date],'System PR Report'[PR_REF_NO],'Perf Metrics'[PR_Ref?])
 
This time I have no errors but I did a random check on one system PR that's supposed to have a Most Recent Start Date but it wasn't reflected in the Perf Metrics table i.e. the field was blank which was incorrect. Please advise what seems to be the issue, thanks!
shermayne123's avatar
shermayne123
Helper I
3 years ago

Hi Ashish,

 

I further checked with my colleague and confirmed that the problem it is possible for Perf Metrics table not to have all the system PRs due to timing issue. As such, I'll close this query, thanks so much!!