Forum Discussion

RRaj_293's avatar
RRaj_293
Helper III
1 year 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 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.

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

6 Replies

  • BeaBF's avatar
    BeaBF
    Super User
    1 year 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
      Helper III
      1 year ago

      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?

       

      CustomerNameGUIDTest_ProcessTimeTest_Totlprocessing TimeinSec
      ABC00272fd14 Min 58 Sec298
      ABC02a8b18b17 Min 59 Sec1079
      ABC035b8aa0Incomplete Process0
      ABC075ddfa8Incomplete Process0
      ABC0bba233d2 Min 33 Sec153
      XYZ1307af713 Min 24 Sec204
      XYZ148fd7bd2 Min 8 Sec128
      XYZ1655b3055 Min 18 Sec318
      XYZ1be3283854 Min 13 Sec3253
      XYZ1c3ab04e5 Min 11 Sec311
  • RRaj_293's avatar
    RRaj_293
    Helper III
    1 year ago

    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.

    • rajendraongole1's avatar
      rajendraongole1
      Super User
      1 year 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.

       

  • RRaj_293's avatar
    RRaj_293
    Helper III
    1 year ago

    Thank you rajendraongole1  and BeaBF . I created something in similar lines and its now working as expected!

     

    Here;s my working DAX - 

     

    New Email Time =
    VAR newemailtime = MIN('Tbl1'[META_CREATE_DATE])
    RETURN
    CALCULATE(newemailtime,FILTER('Tbl1','Tbl1'[PROC_STS] = "NEW EMAIL"))

     

    Final = 

    IF(Min(Tbl1[PROC_SEQ])= 10 && Max(Tbl1[PROC_SEQ])=100 , CONCATENATE(MINUTE([Complete Status Time]-[New Email Time]) & " Min " , SECOND([Complete Status Time]-[New Email Time])& " Sec"), "Incomplete Process")
  • RRaj_293's avatar
    RRaj_293
    Helper 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.