Forum Discussion

RRaj_293's avatar
RRaj_293
Icon for Helper III rankHelper III
2 years ago
Solved

Incorrect difference in time between 2 values of same column

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 ...
tips & tricks
  • rajendraongole1's avatar
    rajendraongole1
    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.

     

  • BeaBF's avatar
    BeaBF
    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's avatar
RRaj_293
Icon for Helper III rankHelper III
1 year ago

Thank you rajendraongole1  and BeaBF . I think it would be better if I give the whole requirement.  

 

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.