Need to calculate the delta between two dates in different tables.

[RobertReeves]

I have a visual I have created for a client. I need to show the delta between two dates in the visual. When using excel we would use:

• =networkdays(f2,g2)-1 Where F is the Positive Contact Date and G is the Service Scheduled Date.
• Positive contact date is pulled from table A and the service scheudled date is from table B.

I have tried everything I can think of. I tried to pull duplicate the contact date column in table B but cannot get Power BI to recognize the field in table A. 

Positive Contact Date	Service Scheduled Date
10/28/2022 19:26	11/1/2022
11/14/2022 11:21	11/16/2022
10/28/2022 15:30	11/1/2022
11/15/2022 19:39	11/16/2022

Any help would be greatly appreciated. 

[v-jingzhang · Accepted solution]

Hi @RobertReeves 

In Power Query, a common method to pull data from one table into another table is to use the Merge queries feature. Merge queries overview - Power Query

To merge queries, you need to select one or multiple columns as the matching columns. Matching columns are used to help match corresponding data to the correct rows.

Once you get correct dates from Table 2 as a new column in Table 1, you can then calculate the duration days between two date columns: Calculate difference between two dates power query - Microsoft Power BI Community

In addition, Power Query doesn't have built-in networkdays functions, but fortunately some users have created custom functions to achieve this result. You can use their custom functions to calculate it faster. Below are two blogs that should be helpful: 

Date.Networkdays function for Power Query and Power BI - Microsoft Power BI Community

Calculate Net Working Days Using Power Query. (mavaanalytics.com)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

[serpiva64]

Hi,

you have posted only one table?! Are the two table ordered? Is there an ID?