topic Re: Need help calculating time between two dates based on conditions in DAX Commands and Tips

Need help calculating time between two dates based on conditions

Anonymous — Thu, 11 May 2023 20:33:15 GMT

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

Re: Need help calculating time between two dates based on conditions

v-jianboli-msft — Mon, 15 May 2023 07:40:21 GMT

Hi Anonymous ,

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.

Re: Need help calculating time between two dates based on conditions

Anonymous — Mon, 15 May 2023 16:25:40 GMT

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!

Re: Need help calculating time between two dates based on conditions

v-jianboli-msft — Tue, 16 May 2023 03:08:01 GMT

Hi Anonymous ,

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

If so, please try:

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.

Re: Need help calculating time between two dates based on conditions

Anonymous — Wed, 17 May 2023 15:33:31 GMT

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

Re: Need help calculating time between two dates based on conditions

v-jianboli-msft — Thu, 18 May 2023 08:11:51 GMT

Hi Anonymous ,

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.