Forum Discussion

RRaj_293's avatar
RRaj_293
Helper 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
Helper III
2 years 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
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.