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
Thank you rajendraongole1 and BeaBF . I created something in similar lines and its now working as expected!
Here;s my working DAX -
Final =