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

Thank you Anonymous  and lbendlin . I think I will start off fresh giving the correct requirement from start . 

 

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.

 

Attaching the actual sample in below post due to char limit