How to Compute the Network days in PBI as new calculated column

[jhoyabel19]

Hi!  PBI Newbie here, 

How do I compute the status duration ( date diff of the two) in Workingdays? I have the following data, some status end date has blanks and for those blanks i want to automatically make it as the Dec 31, 2022 so it will still compute the date difference in Working days.

Thank you for the help.

Anonymised Id	Status	Vacancy Category	Vacancy Type	Status Start Date	Status End Date	Status Duration (Working Days)
418506	Offer Approved	Field	Hiring	07/06/2022	07/06/2022
418506	Offer Awaiting Applicant Decision	Field	Hiring	07/06/2022	07/06/2022
418506	Offer Accepted	Field	Hiring	07/06/2022	12/06/2022
418506	Offer Accepted (On Boarding Forms In Progress )	Field	Hiring	07/06/2022	21/06/2022
418506	Offer Accepted ( Form Completed )	Field	Hiring	07/06/2022	08/06/2022
418506	Preparing Candidate Offer	Field	Hiring	03/06/2022	03/06/2022
418506	Offer Authorising	Field	Hiring	03/06/2022	07/06/2022
398567	Offer Accepted (On Boarding Forms In Progress )	Field	Hiring	25/05/2022	25/05/2022
398567	Offer Accepted ( Form Completed )	Field	Hiring	25/05/2022	26/05/2022
398567	Offer Authorising	Field	Hiring	23/05/2022	23/05/2022
398567	Offer Approved	Field	Hiring	23/05/2022	24/05/2022
398567	Offer Awaiting Applicant Decision	Field	Hiring	24/05/2022	25/05/2022
439372	Offer Approved	Field	Hiring	20/09/2022
394144	Offer Authorisation Failed	Field	Hiring	20/10/2022
330051	Offer Accepted ( Form Completed )	Field	Hiring	30/01/2022
492914	Offer Accepted (On Boarding Forms In Progress )	Field	Hiring	05/09/2022
402877	Offer Accepted ( Form Completed )	Field	Hiring	25/04/2022
560483	Offer Awaiting Applicant Decision	Corporate	Hiring	03/01/2023
545015	Offer Declined	GSC	Hiring	18/11/2022
463509	Offer Accepted ( Form Completed )	GSC	Hiring	27/07/2022

Want outcome is like this in PBI

[DataInsights · Accepted solution]

@jhoyabel19,

Try this calculated column:

Status Duration (Working Days) = 
VAR vStartDate = Table1[Status Start Date]
VAR vEndDate =
    COALESCE ( Table1[Status End Date], DATE ( 2022, 12, 31 ) )
VAR vResult =
    NETWORKDAYS ( vStartDate, vEndDate ) - 1
RETURN
    vResult

[jhoyabel19 · Accepted solution]

Thank you so much. It worked!!

[DataInsights · Accepted solution]

@jhoyabel19,

Try this calculated column:

Status Duration (Working Days) = 
VAR vStartDate = Table1[Status Start Date]
VAR vEndDate =
    COALESCE ( Table1[Status End Date], DATE ( 2022, 12, 31 ) )
VAR vResult =
    NETWORKDAYS ( vStartDate, vEndDate ) - 1
RETURN
    vResult

[jhoyabel19]

@DataInsights , 

May I ask for the blank dates is there a chance if there's a way instead of using fixed Dec 31, 2022 > we can use the Today's date so PBI can automatically compute it? Is it possible?

[DataInsights · Accepted solution]

@jhoyabel19,

You can use the TODAY function:

Status Duration (Working Days) =
VAR vStartDate = Table1[Status Start Date]
VAR vEndDate =
    COALESCE ( Table1[Status End Date], TODAY () )
VAR vResult =
    NETWORKDAYS ( vStartDate, vEndDate ) - 1
RETURN
    vResult

[jhoyabel19 · Accepted solution]

Thank you so much. It worked!!