Forum Discussion

RRaj_293's avatar
RRaj_293
Helper III
1 year ago
Solved

Dax Help - Incorrect difference in time between 2 values of same datetime 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 ...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi  RRaj_293 ,

     

    Here are the steps you can follow：

    1. Create measure.

    Group_Second =
var _new=MAXX(FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])&&'Table'[Process_Status]="NEW EMAIL"),[CREATE_DATETIME])
var _Com=
MAXX(FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])&&'Table'[Process_Status]="COMPLETE"),[CREATE_DATETIME])
RETURN
DATEDIFF(
    _new,_Com,SECOND)
    CustomerIDtime =
FORMAT(TIME(0, 0, [Group_Second]), "HH:mm:ss")
    EachCustomerIDtime =
var _table=
SUMMARIZE(ALLSELECTED('Table'),[Customer],[ID],"Group_S",[Group_Second])
var _avg=
AVERAGEX(
    _table,[Group_S])
return
FORMAT(TIME(0, 0, _avg), "HH:mm:ss")

    2. Result:

     

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

RRaj_293's avatar
RRaj_293
Helper III
1 year ago

Thanks Anonymous  I did something very similar and its now resolved. 

 

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
lbendlin's avatar
lbendlin
Super User
1 year ago

Your sample data is not covering your scenario.  None of the averages exceed 60 minutes.