Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
shermayne123
Helper I
Helper I

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!!
2 ACCEPTED SOLUTIONS

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

18 REPLIES 18
shermayne123
Helper I
Helper I

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!
 
 

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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!

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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!

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
Super User
Super User

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. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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!

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
ABC100013/1/2023
DEF200013/2/2023
DEF200023/3/2023
DEF20003

3/4/2023

 

Perf Metrics Table

(PR) PR No.(PR) PR Item No.Contract Signed Date
ABC100013/14/2023
DEF200013/10/2023
DEF200023/10/2023
DEF200033/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!!

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Padycosmos
Solution Sage
Solution Sage

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
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.