topic Re: Dax Help - Incorrect difference in time between 2 values of same datetime column in Developer

Dax Help - Incorrect difference in time between 2 values of same datetime column

RRaj_293 — Fri, 06 Sep 2024 03:54:38 GMT

Hello,

I'm trying to find the difference in time(single column-META_CREATE_DATE) between the two process status - 'New Email' which is the start of the process and 'Complete' which is the end of the process for each 'GUID'.

These are the measures I have created to calculate the 'processing time'.

New Email Time =

VAR newemailtime = MIN('Tbl1'[META_CREATE_DATE])

RETURN

CALCULATE(newemailtime,FILTER('Tbl1','Tbl1'[PROCESS_STATUS] = "NEW EMAIL"))

Complete Status Time =

VAR completetime = MAX('Tbl1'[META_CREATE_DATE])

RETURN

CALCULATE(completetime, FILTER('Tbl1','Tbl1'[PROCESS_STATUS] = "COMPLETE"))

Processing Time = CONCATENATE(MINUTE([Complete Status Time]-[New Email Time]) & " Min " , SECOND([Complete Status Time]-[New Email Time])& " Sec")

The Issue is for some of the GUIDs both 'New Email Time' and 'Complete Status Time' is picking up the same datetime value which is of 'New Email' status. Ex- for GUID highlighted for both status calculations same value is being picked hence showing 0Min0Sec

How do I fix this? I'm not sure what's wrong with my DAX!

Any advice appreciated.

Re: Dax Help - Incorrect difference in time between 2 values of same datetime column

lbendlin — Fri, 06 Sep 2024 13:45:53 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Dax Help - Incorrect difference in time between 2 values of same datetime column

Anonymous — Mon, 09 Sep 2024 05:27:18 GMT

Thanks for the reply from lbendlin , please allow me to provide another insight:
Hi @RRaj_293 ,

Here are the steps you can follow：

1. Create measure.

New Email Time = MINX( FILTER(ALL('Tbl1'), 'Tbl1'[GUID]=MAX('Tbl1'[GUID])&&'Tbl1'[PROCESS_STATUS]="NEW EMAIL"),[META_CREATE_DATE])

Complete Status Time = MINX( FILTER(ALL('Tbl1'), 'Tbl1'[GUID]=MAX('Tbl1'[GUID])&&'Tbl1'[PROCESS_STATUS]="COMPLETE"),[META_CREATE_DATE])

Processing Time = CONCATENATE(MINUTE([Complete Status Time]-[New Email Time]) & " Min " , SECOND([Complete Status Time]-[New Email Time])& " Sec")

2. Result:

Best Regards,

Liu Yang

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

Re: Dax Help - Incorrect difference in time between 2 values of same datetime column

RRaj_293 — Mon, 09 Sep 2024 06:35:39 GMT

Thanks @Anonymous I did something very similar and its now resolved.

However , Moving on with that solution I need to find the average processing time for each customer (total processing time/distinct count GUID) and display the final average time in Hours:Min:Sec.

Here's is whats is done so far -

Fore each customer's GUID which goes through a process sequence from 10(Process status = New Email) and 100 (Process status = Complete) , I have managed to find the time difference in Min:Sec format and the ones which dont complete the cycle (i.e process status <> Complete as last step) its marked as "Incomplete process". Its calculation DAX is as below -

Test_ProcessTime = IF(Min(Tbl1[PROCESS_SEQUENCE])= 10 && Max(Tbl1[PROCESS_SEQUENCE])=100 , CONCATENATE(MINUTE([Complete Status Time]-[New Email Time]) & " Min " , SECOND([Complete Status Time]-[New Email Time])& " Sec"), "Incomplete Process")

I should now find the average time for each customer ignoring the "Incomplete process" ones. So I converted the time in Seconds and "Incomplete process" is made 0 for ease of calculation in the measure "Test_Totlprocessing TimeinSec" .

Test_Totlprocessing TimeinSec = IF(Min(Tbl1[PROCESS_SEQUENCE])= 10 && Max(Tbl1[PROCESS_SEQUENCE])=100 , MINUTE([Complete Status Time]-[New Email Time])*60 + SECOND([Complete Status Time]-[New Email Time]),0)

Test_Avgprocessing Time =

VAR cnt = DISTINCTCOUNT('Tbl1'[GUID])

RETURN

SUM(Test_Totlprocessing TimeinSec)/cnt ?

Question - The average calculation is not working right. The seconds are not summing up correctly for each customer- Numerator SUM(Test_Totlprocessing TimeinSec) is giving incorrect result. Counts of distinct GUID (denominator) is correct but the sum of total processing time in seconds is not correct and hence average goes wrong. I'm not sure why its incorrect. For customers which have 'Incomplete process' the sum shows as 0.

Please help! How do I find the correct average and display it in HH:MM:SS format?

CustomerName	GUID	Test_ProcessTime	Test_Totlprocessing TimeinSec
ABC	00272fd1	4 Min 58 Sec	298
ABC	02a8b18b	17 Min 59 Sec	1079
ABC	035b8aa0	Incomplete Process	0
ABC	075ddfa8	Incomplete Process	0
ABC	0bba233d	2 Min 33 Sec	153
XYZ	1307af71	3 Min 24 Sec	204
XYZ	148fd7bd	2 Min 8 Sec	128
XYZ	1655b305	5 Min 18 Sec	318
XYZ	1be32838	54 Min 13 Sec	3253
XYZ	1c3ab04e	5 Min 11 Sec	311

Re: Dax Help - Incorrect difference in time between 2 values of same datetime column

lbendlin — Mon, 09 Sep 2024 12:24:49 GMT

Your sample data is not covering your scenario. None of the averages exceed 60 minutes.

Re: Dax Help - Incorrect difference in time between 2 values of same datetime column

RRaj_293 — Wed, 11 Sep 2024 07:13:24 GMT

Thank you @Anonymous and @lbendlin . I think I will start off fresh giving the correct requirement from start .

Basically , In the sample data below - I want find the average time taken for prrocessing at a customer level. A process is said to be complete only when it starts with a 'New Email' , Process seq = 10 and ends with 'Complete' Status -Process seq = 100. If anything else its an incomplete process and can be considered as 0(processing time) for ease of calculation.

In the below sample data - Expected output - for Customer 'WX V5' , ID = '00d8d10c%' time taken for processing =

2024-08-13 13:03:13 (createdatetime where process status = complete) - 2024-08-13 13:01:38 (createdatetime where process status = New Email) = 0:02:20 (hh:mm:ss) . Similarly for each ID for each customer we need to calculate the time taken for each successfull cycle and then find the average time for each customer. One customer could process multiple IDs. Hence ID is the lowest grain.

Please advice . Hope the sample data helps give better clarity.

Attaching the actual sample in below post due to char limit

Re: Dax Help - Incorrect difference in time between 2 values of same datetime column

RRaj_293 — Wed, 11 Sep 2024 07:18:58 GMT

Sample data below -

CREATE_DATETIME	ID	Customer	Process_Status	Process_sequence
2024-07-02 15:17:37.120000 Z	00272fd1		NEW EMAIL	10
2024-07-02 15:17:42.357000 Z	00272fd1		PROCESSING	20
2024-07-02 15:17:48.191000 Z	00272fd1		WORKQUEUE	25
2024-07-02 15:18:02.865000 Z	00272fd1	ABC	START	30
2024-07-02 15:18:23.588000 Z	00272fd1	ABC	END	40
2024-07-02 15:19:19.690000 Z	00272fd1	ABC	PROCESSING	50
2024-07-02 15:22:26.991000 Z	00272fd1		OUTPUT	60
2024-07-02 15:22:35.944000 Z	00272fd1		COMPLETE	100
2024-08-13 13:01:38.375000 Z	00d8d10c		NEW EMAIL	10
2024-08-13 13:01:42.422000 Z	00d8d10c		PROCESSING	20
2024-08-13 13:01:47.782000 Z	00d8d10c		WORKQUEUE	25
2024-08-13 13:02:03.955000 Z	00d8d10c	WX V5	START	30
2024-08-13 13:02:22.293000 Z	00d8d10c	WX V5	END	40
2024-08-13 13:02:35.924000 Z	00d8d10c	WX V5	PROCESSING	50
2024-08-13 13:02:48.025000 Z	00d8d10c	WX V5	PROCESSING	50
2024-08-13 13:03:05.791000 Z	00d8d10c		OUTPUT	60
2024-08-13 13:03:13.863000 Z	00d8d10c		COMPLETE	100
2024-08-15 10:06:35.888000 Z	0111fa1b		NEW EMAIL	10
2024-08-15 10:06:38.952000 Z	0111fa1b		PROCESSING	20
2024-08-15 10:06:44.455000 Z	0111fa1b		WORKQUEUE	25
2024-08-15 10:07:04.127000 Z	0111fa1b	WX V5	START	30
2024-08-15 10:07:27.007000 Z	0111fa1b	WX V5	END	40
2024-08-15 10:08:05.357000 Z	0111fa1b	WX V5	PROCESSING	50
2024-08-15 10:10:00.557000 Z	0111fa1b	WX V5	VALIDATING	51
2024-08-15 10:10:09.781000 Z	0111fa1b		OUTPUT	60
2024-08-15 10:10:20.409000 Z	0111fa1b		COMPLETE	100
2024-08-30 12:05:31.434000 Z	01aa69b1		NEW EMAIL	10
2024-08-30 12:05:40.284000 Z	01aa69b1		PROCESSING	20
2024-08-30 12:05:47.470000 Z	01aa69b1		WORKQUEUE	25
2024-08-30 12:07:03.840000 Z	01aa69b1	WS	START	30
2024-08-30 12:07:28.000000 Z	01aa69b1	WS	END	40
2024-08-30 12:07:45.144000 Z	01aa69b1	WS	PROCESSING	50
2024-08-30 12:07:58.780000 Z	01aa69b1	WS	PROCESSING	50
2024-08-30 12:09:54.803000 Z	01aa69b1	WS	VALIDATING	51
2024-08-30 12:10:06.052000 Z	01aa69b1		OUTPUT	60
2024-08-30 12:10:14.235000 Z	01aa69b1		COMPLETE	100
2024-08-23 10:06:57.087000 Z	02aa89c1		NEW EMAIL	10
2024-08-23 10:07:07.210000 Z	02aa89c1		PROCESSING	20
2024-08-23 10:07:13.948000 Z	02aa89c1		WORKQUEUE	25
2024-08-23 10:08:03.390000 Z	02aa89c1	WS	START	30
2024-08-23 10:08:25.926000 Z	02aa89c1	WS	END	40
2024-08-23 10:08:45.646000 Z	02aa89c1	WS	PROCESSING	50
2024-08-23 10:11:18.253000 Z	02aa89c1	WS	VALIDATING	51
2024-08-23 10:11:27.212000 Z	02aa89c1		OUTPUT	60
2024-08-23 10:11:35.421000 Z	02aa89c1		COMPLETE	100
2024-09-06 09:05:40.921000 Z	04aa64e2		NEW EMAIL	10
2024-09-06 09:06:01.608000 Z	04aa64e2		PROCESSING	20
2024-09-06 09:06:17.001000 Z	04aa64e2		WORKQUEUE	25
2024-09-06 09:09:02.830000 Z	04aa64e2	WS	START	30
2024-09-06 09:09:26.110000 Z	04aa64e2	WS	END	40
2024-09-06 09:09:42.314000 Z	04aa64e2	WS	PROCESSING	50
2024-09-06 09:11:12.188000 Z	04aa64e2	WS	VALIDATING	51
2024-09-06 09:11:23.437000 Z	04aa64e2		OUTPUT	60
2024-09-06 09:11:31.624000 Z	04aa64e2		COMPLETE	100
2024-09-09 15:24:56.542000 Z	0275fde2		NEW EMAIL	10
2024-09-09 15:25:00.398000 Z	0275fde2		PROCESSING	20
2024-09-09 15:25:05.911000 Z	0275fde2		WORKQUEUE	25
2024-09-09 15:26:03.916000 Z	0275fde2	ATOM	START	30
2024-09-09 15:26:25.727000 Z	0275fde2	ATOM	END	40
2024-09-09 15:26:44.953000 Z	0275fde2	ATOM	PROCESSING	50
2024-09-09 15:27:02.100000 Z	0275fde2	ATOM	VALIDATING	51
2024-09-09 15:27:14.796000 Z	0275fde2		OUTPUT	60
2024-09-09 15:27:24.007000 Z	0275fde2		COMPLETE	100
2024-08-26 14:24:26.619000 Z	2776702		NEW EMAIL	10
2024-08-26 14:24:29.152000 Z	2776702		TERMINATED	1000
2024-09-09 08:38:26.777000 Z	02905c89		NEW EMAIL	10
2024-09-09 08:38:35.286000 Z	02905c89		PROCESSING	20
2024-09-09 08:38:41.509000 Z	02905c89		WORKQUEUE	25
2024-09-09 08:39:02.969000 Z	02905c89	WS	START	30
2024-09-09 08:39:24.109000 Z	02905c89	WS	END	40
2024-09-09 08:39:56.505000 Z	02905c89	WS	PROCESSING	50
2024-09-09 08:41:43.734000 Z	02905c89	WS	VALIDATING	51
2024-09-09 08:41:54.286000 Z	02905c89		OUTPUT	60
2024-09-09 08:42:03.232000 Z	02905c89		COMPLETE	100
2024-09-03 14:41:09.604000 Z	029a9332		NEW EMAIL	10
2024-09-03 14:41:14.782000 Z	029a9332		PROCESSING	20
2024-09-03 14:41:19.817000 Z	029a9332		WORKQUEUE	25
2024-09-03 14:42:03.433000 Z	029a9332	ER	START	30
2024-09-03 14:42:24.733000 Z	029a9332	ER	END	40
2024-09-03 14:43:06.855000 Z	029a9332	ER	PROCESSING	50
2024-09-03 14:43:09.370000 Z	029a9332	ER	VALIDATING	51
2024-09-03 14:43:18.406000 Z	029a9332		OUTPUT	60
2024-09-03 14:43:26.459000 Z	029a9332		COMPLETE	100
2024-05-28 14:42:35.791000 Z	02a8b18b		NEW EMAIL	10
2024-05-28 14:42:38.940000 Z	02a8b18b		PROCESSING	20
2024-05-28 14:42:41.271000 Z	02a8b18b		WORKQUEUE	25
2024-05-28 14:43:02.017000 Z	02a8b18b	ABC	START	30
2024-05-28 14:56:02.302000 Z	02a8b18b	ABC	START	30
2024-05-28 14:43:19.137000 Z	02a8b18b	ABC	END	40
2024-05-28 14:56:19.324000 Z	02a8b18b	ABC	END	40
2024-05-28 14:43:38.362000 Z	02a8b18b	ABC	PROCESSING	50
2024-05-28 14:56:57.784000 Z	02a8b18b	ABC	PROCESSING	50
2024-05-28 14:56:58.880000 Z	02a8b18b	ABC	PROCESSING	50
2024-05-28 15:00:28.284000 Z	02a8b18b		OUTPUT	60
2024-05-28 15:00:35.505000 Z	02a8b18b		COMPLETE	100
2024-08-08 10:45:58.656000 Z	02ca90b5		NEW EMAIL	10
2024-08-08 10:46:02.896000 Z	02ca90b5		PROCESSING	20
2024-08-08 10:46:08.027000 Z	02ca90b5		WORKQUEUE	25
2024-08-08 10:47:02.464000 Z	02ca90b5	ER	START	30
2024-08-08 10:47:20.555000 Z	02ca90b5	ER	END	40
2024-08-08 10:47:34.638000 Z	02ca90b5	ER	PROCESSING	50
2024-08-08 10:47:35.185000 Z	02ca90b5	ER	PROCESSING	50
2024-08-08 10:47:55.097000 Z	02ca90b5	ER	PROCESSING	50
2024-08-08 10:48:08.301000 Z	02ca90b5		OUTPUT	60
2024-08-08 10:48:16.588000 Z	02ca90b5		COMPLETE	100
2024-08-28 11:01:33.710000 Z	02d42fb9		NEW EMAIL	10
2024-08-28 11:01:37.644000 Z	02d42fb9		PROCESSING	20
2024-08-28 11:01:44.407000 Z	02d42fb9		WORKQUEUE	25
2024-08-28 11:29:23.294000 Z	02d42fb9		OUTPUT	60
2024-08-28 11:29:31.142000 Z	02d42fb9		COMPLETE	100
2024-08-26 09:13:31.262000 Z	02e0eefe		NEW EMAIL	10
2024-08-26 09:13:36.453000 Z	02e0eefe		PROCESSING	20
2024-08-26 09:13:42.871000 Z	02e0eefe		WORKQUEUE	25
2024-08-26 09:14:03.104000 Z	02e0eefe	ER	START	30
2024-08-26 09:14:26.322000 Z	02e0eefe	ER	END	40
2024-08-26 09:14:55.388000 Z	02e0eefe	ER	PROCESSING	50
2024-08-26 09:14:56.666000 Z	02e0eefe	ER	PROCESSING	50
2024-08-26 09:16:45.300000 Z	02e0eefe	ER	PROCESSING	50
2024-08-26 09:16:47.847000 Z	02e0eefe	ER	VALIDATING	51
2024-08-26 09:16:56.725000 Z	02e0eefe		OUTPUT	60
2024-08-26 09:17:04.625000 Z	02e0eefe		COMPLETE	100
2024-09-02 16:45:58.453000 Z	030931d0		NEW EMAIL	10
2024-09-02 16:46:02.388000 Z	030931d0		PROCESSING	20
2024-09-02 16:46:09.009000 Z	030931d0		WORKQUEUE	25
2024-09-02 16:47:04.181000 Z	030931d0	ATOM	START	30
2024-09-02 16:47:27.308000 Z	030931d0	ATOM	END	40
2024-09-02 16:47:41.229000 Z	030931d0	ATOM	PROCESSING	50
2024-09-02 16:48:01.264000 Z	030931d0	ATOM	PROCESSING	50
2024-09-02 16:52:42.832000 Z	030931d0	ATOM	PROCESSING	50
2024-09-02 16:53:04.234000 Z	030931d0	ATOM	VALIDATING	51
2024-09-02 16:53:14.260000 Z	030931d0		OUTPUT	60
2024-09-02 16:53:22.330000 Z	030931d0		COMPLETE	100
2024-09-05 11:25:50.555000 Z	034515c7		NEW EMAIL	10
2024-09-05 11:20:52.018000 Z	034515c7		TERMINATED	1000
2024-06-04 14:59:22.039000 Z	035b8aa0		NEW EMAIL	10
2024-06-04 14:59:31.163000 Z	035b8aa0		PROCESSING	20
2024-06-04 14:59:34.654000 Z	035b8aa0		WORKQUEUE	25
2024-06-04 15:00:06.279000 Z	035b8aa0	ABC	START	30
2024-06-11 08:56:45.184000 Z	035b8aa0	ABC	START	30
2024-06-04 15:00:23.739000 Z	035b8aa0	ABC	END	40
2024-06-04 15:01:14.813000 Z	035b8aa0	ABC	PROCESSING	50
2024-06-04 15:01:16.248000 Z	035b8aa0	ABC	PROCESSING	50
2024-06-04 15:03:05.333000 Z	035b8aa0		PROCESSING	50
2024-06-04 15:03:06.662000 Z	035b8aa0	ABC	PROCESSING	50
2024-06-05 01:17:26.474000 Z	035b8aa0		PROCESSING	50
2024-06-05 01:17:28.463000 Z	035b8aa0	ABC	PROCESSING	50
2024-06-05 01:29:25.230000 Z	035b8aa0	ABC	TERMINATED	1000
2024-08-21 17:01:57.255000 Z	03ae8a31		NEW EMAIL	10
2024-08-21 17:02:01.941000 Z	03ae8a31		PROCESSING	20
2024-08-21 17:02:08.763000 Z	03ae8a31		WORKQUEUE	25
2024-08-21 17:03:03.558000 Z	03ae8a31	WX V5	START	30
2024-08-21 17:03:25.693000 Z	03ae8a31	WX V5	END	40
2024-08-21 17:03:37.821000 Z	03ae8a31	WX V5	PROCESSING	50
2024-08-21 17:03:38.863000 Z	03ae8a31	WX V5	PROCESSING	50
2024-08-21 17:03:51.419000 Z	03ae8a31	WX V5	PROCESSING	50
2024-08-21 17:03:54.878000 Z	03ae8a31	WX V5	VALIDATING	51
2024-08-21 17:04:03.155000 Z	03ae8a31		OUTPUT	60
2024-08-21 17:04:13.791000 Z	03ae8a31		COMPLETE	100

Re: Dax Help - Incorrect difference in time between 2 values of same datetime column

Anonymous — Fri, 13 Sep 2024 08:45:25 GMT

Hi @RRaj_293 ,

Here are the steps you can follow：

1. Create measure.

Group_Second = var _new=MAXX(FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])&&'Table'[Process_Status]="NEW EMAIL"),[CREATE_DATETIME]) var _Com= MAXX(FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])&&'Table'[Process_Status]="COMPLETE"),[CREATE_DATETIME]) RETURN DATEDIFF( _new,_Com,SECOND)

CustomerIDtime = FORMAT(TIME(0, 0, [Group_Second]), "HH:mm:ss")

EachCustomerIDtime = var _table= SUMMARIZE(ALLSELECTED('Table'),[Customer],[ID],"Group_S",[Group_Second]) var _avg= AVERAGEX( _table,[Group_S]) return FORMAT(TIME(0, 0, _avg), "HH:mm:ss")

2. Result:

Best Regards,

Liu Yang

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