March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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:
Solved! Go to 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.
Hi,
That error message means that there are duplicate entries appearing in the PR_REF_NO column of the System PR table.
Hi it's me again!
My code is:
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 |
Hi,
That error message means that there are duplicate entries appearing in the PR_REF_NO column of the System PR table.
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.
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!!
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:
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!!
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.
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.
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!!
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.
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.
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?
@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]))
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
91 | |
86 | |
77 | |
49 |
User | Count |
---|---|
166 | |
149 | |
99 | |
73 | |
57 |