Calculate Net Working Days from dates in two different tables

[shermayne123]

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

[Ashish_Mathur · Accepted solution]

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.

[shermayne123]

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 No	PR Item No	PR Ref	Contract Signed Date
PM22001	1	PR-12345	5/5/2022
PM23002	1	PR-67890	4/6/2022
PM21056	2	PR-11121	7/7/2022
System PR table
PR_REF_NO	START_DATE
PR-12345	4/4/2022
PR-67890	4/5/2022
PR-11121	3/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 · Accepted solution]

Hi,

That error message means that there are duplicate entries appearing in the PR_REF_NO column of the System PR table.

[shermayne123]

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]

Hi,

Share some data and show the expected result.

[shermayne123]

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_NO	START_DATE	Selected Start Date
PR-12345	4/4/2022	record to be deleted
PR-12345	5/5/2022	5/5/2022
PR-67890	3/3/2022	record to be deleted
PR-67890	4/5/2022	4/5/2022
PR-11121	3/6/2022	3/6/2022
PR-14589	6/6/2022	6/6/2022

Hope you can help, thanks!!

[shermayne123]

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]

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

[Ashish_Mathur]

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]

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]

Hi,

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

[shermayne123]

Hi Ashish,

Due to security issue, I won't be able to share much actual data but here's a simplified example:

All FYs table:

(PR) PR No.	(PR) PR Item No.	Manual PR Created Date
ABC1000	1	3/1/2023
DEF2000	1	3/2/2023
DEF2000	2	3/3/2023
DEF2000	3	3/4/2023

Perf Metrics Table

(PR) PR No.	(PR) PR Item No.	Contract Signed Date
ABC1000	1	3/14/2023
DEF2000	1	3/10/2023
DEF2000	2	3/10/2023
DEF2000	3	3/8/2023

So what is required is to compute the net working days between Manual PR Created Date and Contract Signed Date excluding weekends(Sat&Sun) and public holidays. Hope that is clear, otherwise feel free to let me know. Thanks so much!!

[Ashish_Mathur · Accepted solution]

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.

[shermayne123]

Hi Ashish,

Thanks so much for your screenshot! I managed to add in this new column to pull in the "Contract Signed Date" with your formula and was able to compute the net working days based on my original formula now.  So the solution is to have the second date column added into the first table before applying my formula. Thank you!

[Ashish_Mathur]

You are welcome.

[Padycosmos]

Hope this video helps:

https://www.youtube.com/watch?v=DSkeFwkCcbY

[shermayne123]

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?

[amitchandak]

@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]

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.