Forum Discussion
Incorrect difference in time between 2 values of same column
- 2 years ago
Hi RRaj_293 - can you check that you are picking up the earliest (MIN) timestamp for the 'New Email' process status and using the GUID to isolate the calculation for each unique row.
New Email Time =
CALCULATE(
MIN('Tbl1'[META_CREATE_DATE]),
FILTER('Tbl1', 'Tbl1'[PROCESS_STATUS] = "NEW EMAIL" && 'Tbl1'[GUID] = EARLIER('Tbl1'[GUID]))
)similarly for latest (MAX) timestamp for the 'Complete' process status while using the same GUID filtering Complete Status Time =
CALCULATE(
MAX('Tbl1'[META_CREATE_DATE]),
FILTER('Tbl1', 'Tbl1'[PROCESS_STATUS] = "COMPLETE" && 'Tbl1'[GUID] = EARLIER('Tbl1'[GUID]))
)Now use datediff function
Processing Time =
VAR StartTime = [New Email Time]
VAR EndTime = [Complete Status Time]
RETURN
IF(
ISBLANK(StartTime) || ISBLANK(EndTime),
"No Data",
FORMAT(
DATEDIFF(StartTime, EndTime, SECOND) / 60, "0") & " Min " &
MOD(DATEDIFF(StartTime, EndTime, SECOND), 60) & " Sec"
)The processing time should now correctly calculate the difference between the 'New Email' and 'Complete' process statuses
Hope this helps.
- 2 years ago
RRaj_293 Try with:
New Email Time =
CALCULATE(
MIN('Tbl1'[META_CREATE_DATE]),
FILTER('Tbl1', 'Tbl1'[PROCESS_STATUS] = "NEW EMAIL")
)Complete Status Time =
CALCULATE(
MAX('Tbl1'[META_CREATE_DATE]),
FILTER('Tbl1', 'Tbl1'[PROCESS_STATUS] = "COMPLETE")
)Processing Time =
IF(
[Complete Status Time] <> [New Email Time],
CONCATENATE(
MINUTE([Complete Status Time] - [New Email Time]) & " Min ",
SECOND([Complete Status Time] - [New Email Time]) & " Sec"
),
"0 Min 0 Sec"
)BBF
RRaj_293 Try with:
New Email Time =
CALCULATE(
MIN('Tbl1'[META_CREATE_DATE]),
FILTER('Tbl1', 'Tbl1'[PROCESS_STATUS] = "NEW EMAIL")
)
Complete Status Time =
CALCULATE(
MAX('Tbl1'[META_CREATE_DATE]),
FILTER('Tbl1', 'Tbl1'[PROCESS_STATUS] = "COMPLETE")
)
Processing Time =
IF(
[Complete Status Time] <> [New Email Time],
CONCATENATE(
MINUTE([Complete Status Time] - [New Email Time]) & " Min ",
SECOND([Complete Status Time] - [New Email Time]) & " Sec"
),
"0 Min 0 Sec"
)
BBF
- RRaj_2932 years ago
Helper III
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])RETURNSUM(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