Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
SamK428113
Frequent Visitor

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

‎05-17-2023 12:27 PM

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

 

SamK428113_0-1684351239028.png

Any assistance would be appreciated.  

 

Dataset below:

 

COMPANYNAMEEFFECTIVEDATERFPRESPONSEDUEEXTERNALDATETASKNAMETASKSTATUSTIME_STAMP
Company C1/1/2023 12:00:00 AM5/20/2022 7:51:00 PM40 - Receive Renewal DocumentsAssigned4/16/2022 7:56:49 PM
Company C1/1/2023 12:00:00 AM5/20/2022 7:51:00 PM  4/16/2022 7:56:51 PM
Company C1/1/2023 12:00:00 AM5/20/2022 7:51:00 PM40 - Receive Renewal DocumentsAssigned4/16/2022 7:56:50 PM
Company C1/1/2023 12:00:00 AM5/20/2022 7:51:00 PM  4/16/2022 7:56:48 PM
Company C1/1/2023 12:00:00 AM5/20/2022 7:51:00 PM50 - Send Renewal Activity NotificationAssigned4/16/2022 7:56:49 PM
Company C1/1/2023 12:00:00 AM5/20/2022 7:51:00 PM30 - Evolve Quoting RequestAssigned4/16/2022 7:56:48 PM
Company C1/1/2023 12:00:00 AM5/20/2022 7:51:00 PM40 - Receive Renewal DocumentsAssigned4/16/2022 7:56:50 PM
Company C1/1/2023 12:00:00 AM5/20/2022 7:51:00 PM40 - Receive Renewal DocumentsAssigned4/16/2022 7:56:50 PM
Company C1/1/2023 12:00:00 AM5/20/2022 7:51:00 PM40 - Receive Renewal DocumentsAssigned4/16/2022 7:56:51 PM
Company C1/1/2023 12:00:00 AM5/20/2022 7:51:00 PM10 - Initiate Customer Package RequestCompleted4/16/2022 7:56:46 PM
Company C1/1/2023 12:00:00 AM5/20/2022 7:51:00 PM  4/16/2022 7:56:47 PM
Company C1/1/2023 12:00:00 AM5/20/2022 7:51:00 PM40 - Receive Renewal DocumentsAssigned4/16/2022 7:56:51 PM
Company C1/1/2023 12:00:00 AM5/20/2022 7:51:00 PM40 - Receive Renewal DocumentsAssigned4/16/2022 7:56:51 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM40 - Receive Renewal DocumentsAssigned4/16/2022 8:15:03 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM  4/16/2022 8:15:03 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM  4/16/2022 8:15:00 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM30 - Evolve Quoting RequestAssigned4/16/2022 8:15:02 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM50 - Send Renewal Activity NotificationAssigned4/16/2022 8:15:02 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM40 - Receive Renewal DocumentsAssigned4/16/2022 8:15:03 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM10 - Initiate Customer Package RequestCompleted4/16/2022 8:14:59 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM  4/16/2022 8:15:01 PM
Company C1/1/2023 12:00:00 AM5/20/2022 7:51:00 PM30 - Evolve Quoting RequestStarted4/27/2022 2:07:26 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM30 - Evolve Quoting RequestSaved4/27/2022 2:07:45 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM40 - Receive Renewal DocumentsStarted5/20/2022 8:30:03 AM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM40 - Receive Renewal DocumentsCompleted5/20/2022 8:31:13 AM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM40 - Receive Renewal DocumentsStarted5/20/2022 8:31:17 AM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM40 - Receive Renewal DocumentsStarted5/23/2022 12:27:37 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM40 - Receive Renewal DocumentsStarted5/23/2022 12:27:45 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM40 - Receive Renewal DocumentsCompleted5/23/2022 12:27:41 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM40 - Receive Renewal DocumentsStarted5/23/2022 12:27:58 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM40 - Receive Renewal DocumentsCompleted5/20/2022 8:38:57 AM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM40 - Receive Renewal DocumentsCompleted5/23/2022 12:27:55 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM40 - Receive Renewal DocumentsCompleted5/23/2022 12:28:16 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM40 - Receive Renewal DocumentsStarted5/23/2022 12:28:28 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM40 - Receive Renewal DocumentsCompleted5/23/2022 12:28:24 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM40 - Receive Renewal DocumentsStarted5/23/2022 12:28:20 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM40 - Receive Renewal DocumentsStarted5/23/2022 12:28:44 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM40 - Receive Renewal DocumentsCompleted5/23/2022 12:28:40 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM40 - Receive Renewal DocumentsCompleted5/23/2022 12:28:32 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM40 - Receive Renewal DocumentsStarted5/23/2022 12:28:36 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM40 - Receive Renewal DocumentsCompleted5/23/2022 12:28:48 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM  5/23/2022 2:15:24 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM510 - Generate Customer PackageCompleted5/23/2022 2:15:23 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM180 - Review QuotesAssigned5/20/2022 1:28:37 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM180 - Review QuotesStarted5/20/2022 1:29:10 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM  5/20/2022 1:31:20 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM30 - Evolve Quoting RequestStarted5/20/2022 1:31:24 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM180 - Review QuotesCompleted5/20/2022 1:31:19 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM30 - Evolve Quoting RequestCompleted5/20/2022 1:31:44 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM30 - Evolve Quoting RequestAssigned5/20/2022 1:31:45 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM510 - Generate Customer PackageAssigned5/20/2022 1:31:46 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM  5/20/2022 1:31:46 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM510 - Generate Customer PackageStarted5/20/2022 1:32:02 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM  5/20/2022 2:25:58 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM510 - Generate Customer PackageCompleted5/20/2022 2:25:57 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM180 - Review QuotesAssigned5/23/2022 1:35:33 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM180 - Review QuotesStarted5/23/2022 1:35:50 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM  5/23/2022 1:36:46 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM180 - Review QuotesCompleted5/23/2022 1:36:45 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM30 - Evolve Quoting RequestStarted5/23/2022 1:36:49 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM30 - Evolve Quoting RequestCompleted5/23/2022 1:37:27 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM30 - Evolve Quoting RequestAssigned5/23/2022 1:37:29 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM  5/23/2022 1:37:29 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM510 - Generate Customer PackageAssigned5/23/2022 1:37:29 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM510 - Generate Customer PackageStarted5/23/2022 1:38:14 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM30 - Evolve Quoting RequestStarted10/13/2022 8:33:53 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM  10/13/2022 8:40:51 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM  10/13/2022 8:40:51 PM
Company D1/1/2023 12:00:00 AM5/25/2022 8:10:00 PM30 - Evolve Quoting RequestCompleted10/13/2022 8:40:50 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM  10/28/2022 8:50:49 AM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM50 - Send Renewal Activity NotificationStarted10/28/2022 8:50:39 AM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM50 - Send Renewal Activity NotificationCompleted10/28/2022 8:50:48 AM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM30 - Evolve Quoting RequestStarted10/31/2022 12:34:25 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM  10/31/2022 12:35:02 PM
Company C1/1/2023 12:00:00 AM5/27/2022 7:51:00 PM30 - Evolve Quoting RequestCompleted10/31/2022 12:35:00 PM
Company D1/1/2024 12:00:00 AM 710 - Update Quoting RequestStarted4/4/2023 1:14:54 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM30 - Evolve Quoting RequestStarted4/4/2023 6:32:21 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM30 - Evolve Quoting RequestStarted4/4/2023 6:32:24 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM30 - Evolve Quoting RequestSaved4/4/2023 6:32:31 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM  4/4/2023 1:39:23 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM710 - Update Quoting RequestCompleted4/4/2023 1:39:21 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM  4/4/2023 1:39:24 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM30 - Evolve Quoting RequestStarted4/4/2023 6:40:13 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM30 - Evolve Quoting RequestSaved4/4/2023 6:41:00 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM30 - Evolve Quoting RequestStarted4/4/2023 6:41:16 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM110 - Attach NPS Packets to QuotesAssigned4/4/2023 6:43:23 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM30 - Evolve Quoting RequestCompleted4/4/2023 6:43:21 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM  4/4/2023 6:43:23 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM30 - Evolve Quoting RequestStarted4/4/2023 6:47:56 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM30 - Evolve Quoting RequestSaved4/4/2023 6:49:41 PM
Company D1/1/2024 12:00:00 AM   4/4/2023 12:14:26 PM
Company D1/1/2024 12:00:00 AM 710 - Update Quoting RequestAssigned4/4/2023 12:14:26 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM30 - Evolve Quoting RequestStarted4/5/2023 6:53:38 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM30 - Evolve Quoting RequestSaved4/5/2023 6:53:47 PM
Company C1/1/2024 12:00:00 AM 710 - Update Quoting RequestStarted4/11/2023 10:08:49 AM
Company C1/1/2024 12:00:00 AM5/31/2023 10:08:00 AM710 - Update Quoting RequestCompleted4/11/2023 10:14:01 AM
Company C1/1/2024 12:00:00 AM5/31/2023 10:08:00 AM710 - Update Quoting RequestAssigned4/11/2023 10:14:03 AM
Company C1/1/2024 12:00:00 AM   4/11/2023 7:31:51 AM
Company C1/1/2024 12:00:00 AM 710 - Update Quoting RequestAssigned4/11/2023 7:31:52 AM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM30 - Evolve Quoting RequestStarted4/19/2023 4:33:24 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM30 - Evolve Quoting RequestSaved4/19/2023 4:34:05 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM30 - Evolve Quoting RequestStarted4/24/2023 10:03:02 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM30 - Evolve Quoting RequestCompleted4/24/2023 10:19:32 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM  4/24/2023 10:19:35 PM
Company D1/1/2024 12:00:00 AM5/8/2023 1:14:00 PM110 - Attach NPS Packets to QuotesAssigned4/24/2023 10:19:35 PM
Message 1 of 3
447 Views
0
1 ACCEPTED SOLUTION
Anonymous
Not applicable

‎05-22-2023 07:35 PM

Hi @SamK428113 ,

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.

View solution in original post

Message 2 of 3
356 Views
0
2 REPLIES 2
SamK428113
Frequent Visitor

‎05-23-2023 01:35 PM

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).
 
SamK428113_0-1684874014990.png

 

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])
 
SamK428113_1-1684874114655.png

 

Message 3 of 3
336 Views
0
Anonymous
Not applicable

‎05-22-2023 07:35 PM

Hi @SamK428113 ,

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.

Message 2 of 3
357 Views
0

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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

View All