Need help calculating time between two dates based on conditions

[SamK428113]

Hello! New to Power BI and need help. 

I'm trying to calculate the time between two dates in point in time based on task name and task status (data sample below) in business days.  I tried creating a measure, but thinking I need to create a calculated column for ABC company. 

- A new row is created everytime a new transaction takes place.

- Trying to calculate turn around time in business days between Min Time Stamp of  Task Name - '110 - Attach NPS Packets to Quotes' & Task Status 'Assigned' and Max Time Stamp of Task Name - '510 Generate Customer Package' & Task Status 'Completed'

-Data should show Turn around time between the two dates below for ABC company.  There are many other companies in this data but this is a subset

110 - Attach NPS Packets to Quotes - 4/11/2023 7:43:56AM

510 - Generate Customer Package - 5/8/2023 12:48:13 PM

COMPANYNAME	EFFECTIVEDATE	RFPRESPONSEDUEEXTERNALDATE	TASKNAME	TASKSTATUS	TIME_STAMP
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM			4/15/2022 11:18:25 AM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	50 - Send Renewal Activity Notification	Assigned	4/15/2022 11:18:26 AM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	30 - Evolve Quoting Request	Assigned	4/15/2022 11:18:27 AM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM			4/15/2022 11:18:28 AM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	40 - Receive Renewal Documents	Assigned	4/15/2022 11:18:27 AM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	40 - Receive Renewal Documents	Assigned	4/15/2022 11:18:27 AM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	10 - Initiate Customer Package Request	Completed	4/15/2022 11:18:24 AM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM			4/15/2022 11:18:26 AM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	50 - Send Renewal Activity Notification	Started	4/15/2022 11:20:51 AM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	50 - Send Renewal Activity Notification	Completed	4/15/2022 11:20:58 AM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	40 - Receive Renewal Documents	Completed	4/27/2022 12:24:59 PM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	40 - Receive Renewal Documents	Started	4/27/2022 12:24:53 PM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	40 - Receive Renewal Documents	Started	4/27/2022 12:25:04 PM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM			4/27/2022 12:25:10 PM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	40 - Receive Renewal Documents	Completed	4/27/2022 12:25:10 PM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	180 - Review Quotes	Assigned	4/27/2022 2:47:44 PM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	180 - Review Quotes	Started	4/27/2022 2:47:53 PM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	180 - Review Quotes	Completed	4/27/2022 2:48:19 PM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	30 - Evolve Quoting Request	Started	4/27/2022 2:48:25 PM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM			4/27/2022 2:48:21 PM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	30 - Evolve Quoting Request	Assigned	4/27/2022 2:48:43 PM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	510 - Generate Customer Package	Assigned	4/27/2022 2:48:44 PM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM			4/27/2022 2:48:43 PM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	30 - Evolve Quoting Request	Completed	4/27/2022 2:48:42 PM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	510 - Generate Customer Package	Started	4/27/2022 2:49:04 PM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM			4/27/2022 2:50:46 PM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	510 - Generate Customer Package	Completed	4/27/2022 2:50:46 PM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	30 - Evolve Quoting Request	Started	10/5/2022 10:45:23 AM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM	30 - Evolve Quoting Request	Completed	10/5/2022 10:45:57 AM
ABC	1/1/2023 12:00:00 AM	5/27/2022 11:15:00 AM			10/5/2022 10:45:58 AM
ABC	1/1/2024 12:00:00 AM	5/15/2023 7:41:00 AM	110 - Attach NPS Packets to Quotes	Started	4/12/2023 5:55:38 AM
ABC	1/1/2024 12:00:00 AM		710 - Update Quoting Request	Started	4/11/2023 7:41:48 AM
ABC	1/1/2024 12:00:00 AM	5/15/2023 7:41:00 AM	110 - Attach NPS Packets to Quotes	Assigned	4/11/2023 7:43:56 AM
ABC	1/1/2024 12:00:00 AM	5/15/2023 7:41:00 AM			4/11/2023 7:43:56 AM
ABC	1/1/2024 12:00:00 AM	5/15/2023 7:41:00 AM			4/11/2023 7:43:53 AM
ABC	1/1/2024 12:00:00 AM	5/15/2023 7:41:00 AM	710 - Update Quoting Request	Completed	4/11/2023 7:43:50 AM
ABC	1/1/2024 12:00:00 AM	5/15/2023 7:41:00 AM			4/11/2023 7:43:54 AM
ABC	1/1/2024 12:00:00 AM				4/11/2023 7:32:52 AM
ABC	1/1/2024 12:00:00 AM		710 - Update Quoting Request	Assigned	4/11/2023 7:32:52 AM
ABC	1/1/2024 12:00:00 AM	5/15/2023 7:41:00 AM	30 - Evolve Quoting Request	Started	4/13/2023 9:56:22 AM
ABC	1/1/2024 12:00:00 AM	5/15/2023 7:41:00 AM	30 - Evolve Quoting Request	Completed	4/13/2023 9:58:29 AM
ABC	1/1/2024 12:00:00 AM	5/15/2023 7:41:00 AM	110 - Attach NPS Packets to Quotes	Started	4/13/2023 1:32:31 PM

[SamK428113]

Hi Jianbo - One additional question.  Instead of calculating calendar days or minutes, is it possible to calculate by networkdays?  

[v-jianboli-msft]

Hi @SamK428113 ,

This DAX function may help you:

NETWORKDAYS

You can try to replace DATEDIFF with this fuction.

Best Regards,

Jianbo Li

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

[v-jianboli-msft]

Hi @SamK428113 ,

Please try:

time between two dates =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[TIME_STAMP] ),
        FILTER (
            ALL ( 'Table' ),
            [COMPANYNAME] = SELECTEDVALUE ( 'Table'[COMPANYNAME] )
                && [TASKNAME] = "110 - Attach NPS Packets to Quotes"
                && [TASKSTATUS] = "Assigned"
        )
    )
VAR _b =
    CALCULATE (
        MAX ( 'Table'[TIME_STAMP] ),
        FILTER (
            ALL ( 'Table' ),
            [COMPANYNAME] = SELECTEDVALUE ( 'Table'[COMPANYNAME] )
                && [TASKNAME] = "510 - Generate Customer Package"
                && [TASKSTATUS] = "Completed"
        )
    )
RETURN
    DATEDIFF ( _b, _a, MINUTE )

Final output:

Best Regards,

Jianbo Li

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

[SamK428113]

Hi Jianbo - Thank you!  I was able to replicate the calculation and seems to be working.  However, some of these companies are the same but from the previous year.  For this year, some of the companies, after a 110 assigned, but has not completed a 510 task yet.  It seems to be pulling from the 510 from the previous year.  Is there a way to omit the calculation and not pull from last year's 510 for the same company? 

Thank you in advance!

[v-jianboli-msft · Accepted solution]

Hi @SamK428113 ,

From your description, it seems that the "510 task" to be calculated should occur after the "110 assigned", right?

If so, please try:

time between two dates =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[TIME_STAMP] ),
        FILTER (
            ALL ( 'Table' ),
            [COMPANYNAME] = SELECTEDVALUE ( 'Table'[COMPANYNAME] )
                && [TASKNAME] = "110 - Attach NPS Packets to Quotes"
                && [TASKSTATUS] = "Assigned"
        )
    )
VAR _b =
    CALCULATE (
        MAX ( 'Table'[TIME_STAMP] ),
        FILTER (
            ALL ( 'Table' ),
            [COMPANYNAME] = SELECTEDVALUE ( 'Table'[COMPANYNAME] )
                && [TASKNAME] = "510 - Generate Customer Package"
                && [TASKSTATUS] = "Completed"
        )
    )
VAR _c =
    IF ( _b > _a, _b )
RETURN
    DATEDIFF ( _c, _a, MINUTE )

Best Regards,

Jianbo Li

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