Trying to calculate date difference in network days only for companies with start and end dates.

Anonymous — Wed, 17 May 2023 19:27:08 GMT

Trying to create a way to calculate networkdays for each company between a 110 assigned and 510 completed task names.

Calendar Days Measure:

Test 9=

VAR _a =

CALCULATE(

MIN('CWMT_MS_QUOTINGREQUESTTRACKER - Master'[TIME_STAMP]),

FILTER(ALL('CWMT_MS_QUOTINGREQUESTTRACKER - Master'),

[COMPANYNAME] = SELECTEDVALUE('CWMT_MS_QUOTINGREQUESTTRACKER - Master'[COMPANYNAME])

&& 'CWMT_MS_QUOTINGREQUESTTRACKER - Master'[TASKNAME] = "110 - Attach NPS Packets to Quotes"

&& 'CWMT_MS_QUOTINGREQUESTTRACKER - Master'[TASKSTATUS] = "Assigned"))

Var _b =

CALCULATE(

MAX('CWMT_MS_QUOTINGREQUESTTRACKER - Master'[TIME_STAMP]),

FILTER(ALL('CWMT_MS_QUOTINGREQUESTTRACKER - Master'),

[COMPANYNAME] = SELECTEDVALUE('CWMT_MS_QUOTINGREQUESTTRACKER - Master'[COMPANYNAME])

&& 'CWMT_MS_QUOTINGREQUESTTRACKER - Master'[TASKNAME] = "510 - Generate Customer Package"

&& 'CWMT_MS_QUOTINGREQUESTTRACKER - Master'[TASKSTATUS] = "Completed"))

Var _c =

IF (_b > _a, _b)

RETURN

DATEDIFF(_a, _c, DAY)

I'm trying to create a measure to calculate network days but excludes companies that do not have both dates. Here is the measure I'm using now and doesn't seem to be working:

Network Days = (NETWORKDAYS([110 Assigned], [510 Completed]))

Any assistance would be appreciated.

Dataset below:

COMPANYNAME	EFFECTIVEDATE	RFPRESPONSEDUEEXTERNALDATE	TASKNAME	TASKSTATUS	TIME_STAMP
Company C	1/1/2023 12:00:00 AM	5/20/2022 7:51:00 PM	40 - Receive Renewal Documents	Assigned	4/16/2022 7:56:49 PM
Company C	1/1/2023 12:00:00 AM	5/20/2022 7:51:00 PM			4/16/2022 7:56:51 PM
Company C	1/1/2023 12:00:00 AM	5/20/2022 7:51:00 PM	40 - Receive Renewal Documents	Assigned	4/16/2022 7:56:50 PM
Company C	1/1/2023 12:00:00 AM	5/20/2022 7:51:00 PM			4/16/2022 7:56:48 PM
Company C	1/1/2023 12:00:00 AM	5/20/2022 7:51:00 PM	50 - Send Renewal Activity Notification	Assigned	4/16/2022 7:56:49 PM
Company C	1/1/2023 12:00:00 AM	5/20/2022 7:51:00 PM	30 - Evolve Quoting Request	Assigned	4/16/2022 7:56:48 PM
Company C	1/1/2023 12:00:00 AM	5/20/2022 7:51:00 PM	40 - Receive Renewal Documents	Assigned	4/16/2022 7:56:50 PM
Company C	1/1/2023 12:00:00 AM	5/20/2022 7:51:00 PM	40 - Receive Renewal Documents	Assigned	4/16/2022 7:56:50 PM
Company C	1/1/2023 12:00:00 AM	5/20/2022 7:51:00 PM	40 - Receive Renewal Documents	Assigned	4/16/2022 7:56:51 PM
Company C	1/1/2023 12:00:00 AM	5/20/2022 7:51:00 PM	10 - Initiate Customer Package Request	Completed	4/16/2022 7:56:46 PM
Company C	1/1/2023 12:00:00 AM	5/20/2022 7:51:00 PM			4/16/2022 7:56:47 PM
Company C	1/1/2023 12:00:00 AM	5/20/2022 7:51:00 PM	40 - Receive Renewal Documents	Assigned	4/16/2022 7:56:51 PM
Company C	1/1/2023 12:00:00 AM	5/20/2022 7:51:00 PM	40 - Receive Renewal Documents	Assigned	4/16/2022 7:56:51 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM	40 - Receive Renewal Documents	Assigned	4/16/2022 8:15:03 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM			4/16/2022 8:15:03 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM			4/16/2022 8:15:00 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM	30 - Evolve Quoting Request	Assigned	4/16/2022 8:15:02 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM	50 - Send Renewal Activity Notification	Assigned	4/16/2022 8:15:02 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM	40 - Receive Renewal Documents	Assigned	4/16/2022 8:15:03 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM	10 - Initiate Customer Package Request	Completed	4/16/2022 8:14:59 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM			4/16/2022 8:15:01 PM
Company C	1/1/2023 12:00:00 AM	5/20/2022 7:51:00 PM	30 - Evolve Quoting Request	Started	4/27/2022 2:07:26 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	30 - Evolve Quoting Request	Saved	4/27/2022 2:07:45 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM	40 - Receive Renewal Documents	Started	5/20/2022 8:30:03 AM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM	40 - Receive Renewal Documents	Completed	5/20/2022 8:31:13 AM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM	40 - Receive Renewal Documents	Started	5/20/2022 8:31:17 AM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	40 - Receive Renewal Documents	Started	5/23/2022 12:27:37 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	40 - Receive Renewal Documents	Started	5/23/2022 12:27:45 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	40 - Receive Renewal Documents	Completed	5/23/2022 12:27:41 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	40 - Receive Renewal Documents	Started	5/23/2022 12:27:58 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM	40 - Receive Renewal Documents	Completed	5/20/2022 8:38:57 AM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	40 - Receive Renewal Documents	Completed	5/23/2022 12:27:55 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	40 - Receive Renewal Documents	Completed	5/23/2022 12:28:16 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	40 - Receive Renewal Documents	Started	5/23/2022 12:28:28 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	40 - Receive Renewal Documents	Completed	5/23/2022 12:28:24 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	40 - Receive Renewal Documents	Started	5/23/2022 12:28:20 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	40 - Receive Renewal Documents	Started	5/23/2022 12:28:44 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	40 - Receive Renewal Documents	Completed	5/23/2022 12:28:40 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	40 - Receive Renewal Documents	Completed	5/23/2022 12:28:32 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	40 - Receive Renewal Documents	Started	5/23/2022 12:28:36 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	40 - Receive Renewal Documents	Completed	5/23/2022 12:28:48 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM			5/23/2022 2:15:24 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	510 - Generate Customer Package	Completed	5/23/2022 2:15:23 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM	180 - Review Quotes	Assigned	5/20/2022 1:28:37 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM	180 - Review Quotes	Started	5/20/2022 1:29:10 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM			5/20/2022 1:31:20 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM	30 - Evolve Quoting Request	Started	5/20/2022 1:31:24 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM	180 - Review Quotes	Completed	5/20/2022 1:31:19 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM	30 - Evolve Quoting Request	Completed	5/20/2022 1:31:44 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM	30 - Evolve Quoting Request	Assigned	5/20/2022 1:31:45 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM	510 - Generate Customer Package	Assigned	5/20/2022 1:31:46 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM			5/20/2022 1:31:46 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM	510 - Generate Customer Package	Started	5/20/2022 1:32:02 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM			5/20/2022 2:25:58 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM	510 - Generate Customer Package	Completed	5/20/2022 2:25:57 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	180 - Review Quotes	Assigned	5/23/2022 1:35:33 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	180 - Review Quotes	Started	5/23/2022 1:35:50 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM			5/23/2022 1:36:46 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	180 - Review Quotes	Completed	5/23/2022 1:36:45 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	30 - Evolve Quoting Request	Started	5/23/2022 1:36:49 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	30 - Evolve Quoting Request	Completed	5/23/2022 1:37:27 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	30 - Evolve Quoting Request	Assigned	5/23/2022 1:37:29 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM			5/23/2022 1:37:29 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	510 - Generate Customer Package	Assigned	5/23/2022 1:37:29 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	510 - Generate Customer Package	Started	5/23/2022 1:38:14 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM	30 - Evolve Quoting Request	Started	10/13/2022 8:33:53 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM			10/13/2022 8:40:51 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM			10/13/2022 8:40:51 PM
Company D	1/1/2023 12:00:00 AM	5/25/2022 8:10:00 PM	30 - Evolve Quoting Request	Completed	10/13/2022 8:40:50 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM			10/28/2022 8:50:49 AM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	50 - Send Renewal Activity Notification	Started	10/28/2022 8:50:39 AM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	50 - Send Renewal Activity Notification	Completed	10/28/2022 8:50:48 AM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	30 - Evolve Quoting Request	Started	10/31/2022 12:34:25 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM			10/31/2022 12:35:02 PM
Company C	1/1/2023 12:00:00 AM	5/27/2022 7:51:00 PM	30 - Evolve Quoting Request	Completed	10/31/2022 12:35:00 PM
Company D	1/1/2024 12:00:00 AM		710 - Update Quoting Request	Started	4/4/2023 1:14:54 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM	30 - Evolve Quoting Request	Started	4/4/2023 6:32:21 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM	30 - Evolve Quoting Request	Started	4/4/2023 6:32:24 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM	30 - Evolve Quoting Request	Saved	4/4/2023 6:32:31 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM			4/4/2023 1:39:23 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM	710 - Update Quoting Request	Completed	4/4/2023 1:39:21 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM			4/4/2023 1:39:24 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM	30 - Evolve Quoting Request	Started	4/4/2023 6:40:13 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM	30 - Evolve Quoting Request	Saved	4/4/2023 6:41:00 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM	30 - Evolve Quoting Request	Started	4/4/2023 6:41:16 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM	110 - Attach NPS Packets to Quotes	Assigned	4/4/2023 6:43:23 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM	30 - Evolve Quoting Request	Completed	4/4/2023 6:43:21 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM			4/4/2023 6:43:23 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM	30 - Evolve Quoting Request	Started	4/4/2023 6:47:56 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM	30 - Evolve Quoting Request	Saved	4/4/2023 6:49:41 PM
Company D	1/1/2024 12:00:00 AM				4/4/2023 12:14:26 PM
Company D	1/1/2024 12:00:00 AM		710 - Update Quoting Request	Assigned	4/4/2023 12:14:26 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM	30 - Evolve Quoting Request	Started	4/5/2023 6:53:38 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM	30 - Evolve Quoting Request	Saved	4/5/2023 6:53:47 PM
Company C	1/1/2024 12:00:00 AM		710 - Update Quoting Request	Started	4/11/2023 10:08:49 AM
Company C	1/1/2024 12:00:00 AM	5/31/2023 10:08:00 AM	710 - Update Quoting Request	Completed	4/11/2023 10:14:01 AM
Company C	1/1/2024 12:00:00 AM	5/31/2023 10:08:00 AM	710 - Update Quoting Request	Assigned	4/11/2023 10:14:03 AM
Company C	1/1/2024 12:00:00 AM				4/11/2023 7:31:51 AM
Company C	1/1/2024 12:00:00 AM		710 - Update Quoting Request	Assigned	4/11/2023 7:31:52 AM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM	30 - Evolve Quoting Request	Started	4/19/2023 4:33:24 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM	30 - Evolve Quoting Request	Saved	4/19/2023 4:34:05 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM	30 - Evolve Quoting Request	Started	4/24/2023 10:03:02 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM	30 - Evolve Quoting Request	Completed	4/24/2023 10:19:32 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM			4/24/2023 10:19:35 PM
Company D	1/1/2024 12:00:00 AM	5/8/2023 1:14:00 PM	110 - Attach NPS Packets to Quotes	Assigned	4/24/2023 10:19:35 PM

Re: Trying to calculate date difference in network days only for companies with start and end dates.

Anonymous — Tue, 23 May 2023 02:35:37 GMT

Hi Anonymous ,

To calculate the network days between two dates for companies with both start and end dates, you can modify the existing measure to include a check for the presence of both dates. Here's an example of how you can modify the measure:

Network Days = IF ( ISBLANK ( [110 Assigned] ) || ISBLANK ( [510 Completed] ), BLANK (), NETWORKDAYS ( [110 Assigned], [510 Completed] ) )

This measure checks if either the start or end date is blank, and returns a blank value if either is missing. If both dates are present, it calculates the network days between them using the function.

You can use this measure in your report to calculate the network days between the assigned and completed tasks for each company.

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Trying to calculate date difference in network days only for companies with start and end dates.

Anonymous — Tue, 23 May 2023 20:35:21 GMT

Hi Rongtie,

The solution works and I am able to calculate the network days for non blanks between Start and End dates. However, when I try to get the average number of networkdays in a given column, the value returns as blank.

Test 10 =

VAR _a =

CALCULATE(

MIN('CWMT_MS_QUOTINGREQUESTTRACKER - Master'[TIME_STAMP]),

FILTER(ALL('CWMT_MS_QUOTINGREQUESTTRACKER - Master'),

[COMPANYNAME] = SELECTEDVALUE('CWMT_MS_QUOTINGREQUESTTRACKER - Master'[COMPANYNAME])

&& 'CWMT_MS_QUOTINGREQUESTTRACKER - Master'[TASKNAME] = "110 - Attach NPS Packets to Quotes"

&& 'CWMT_MS_QUOTINGREQUESTTRACKER - Master'[TASKSTATUS] = "Assigned"))

Var _b =

CALCULATE(

MAX('CWMT_MS_QUOTINGREQUESTTRACKER - Master'[TIME_STAMP]),

FILTER(ALL('CWMT_MS_QUOTINGREQUESTTRACKER - Master'),

[COMPANYNAME] = SELECTEDVALUE('CWMT_MS_QUOTINGREQUESTTRACKER - Master'[COMPANYNAME])

&& 'CWMT_MS_QUOTINGREQUESTTRACKER - Master'[TASKNAME] = "510 - Generate Customer Package"

&& 'CWMT_MS_QUOTINGREQUESTTRACKER - Master'[TASKSTATUS] = "Completed"))

RETURN

if (

ISBLANK([Test 110 Assigned])||ISBLANK([Test 510 Completed]),

BLANK(),

NETWORKDAYS([Test 110 Assigned],[Test 510 Completed]))

Test 4 = AVERAGEX('CWMT_MS_QUOTINGREQUESTTRACKER - Master', [Test 10])

Test 4 returns as (Blank).

Any idea why the value is showing blank?

For calendar days, I'm using this measure and it seems to be working...

Test 9 =

VAR _a =

CALCULATE(

MIN('CWMT_MS_QUOTINGREQUESTTRACKER - Master'[TIME_STAMP]),

FILTER(ALL('CWMT_MS_QUOTINGREQUESTTRACKER - Master'),

[COMPANYNAME] = SELECTEDVALUE('CWMT_MS_QUOTINGREQUESTTRACKER - Master'[COMPANYNAME])

&& 'CWMT_MS_QUOTINGREQUESTTRACKER - Master'[TASKNAME] = "110 - Attach NPS Packets to Quotes"

&& 'CWMT_MS_QUOTINGREQUESTTRACKER - Master'[TASKSTATUS] = "Assigned"))

Var _b =

CALCULATE(

MAX('CWMT_MS_QUOTINGREQUESTTRACKER - Master'[TIME_STAMP]),

FILTER(ALL('CWMT_MS_QUOTINGREQUESTTRACKER - Master'),

[COMPANYNAME] = SELECTEDVALUE('CWMT_MS_QUOTINGREQUESTTRACKER - Master'[COMPANYNAME])

&& 'CWMT_MS_QUOTINGREQUESTTRACKER - Master'[TASKNAME] = "510 - Generate Customer Package"

&& 'CWMT_MS_QUOTINGREQUESTTRACKER - Master'[TASKSTATUS] = "Completed"))

Var _c =

IF (_b > _a, _b)

RETURN

DATEDIFF(_a, _c, DAY)

Test 3 = AVERAGEX('CWMT_MS_QUOTINGREQUESTTRACKER - Master', [Test 9])

topic Trying to calculate date difference in network days only for companies with start and end dates. in DAX Commands and Tips

Trying to calculate date difference in network days only for companies with start and end dates.

Re: Trying to calculate date difference in network days only for companies with start and end dates.

Re: Trying to calculate date difference in network days only for companies with start and end dates.