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'.
How do I fix this? I'm not sure what's wrong with my DAX!
Any advice appreciated.
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
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
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
Thanks for the reply from lbendlin , please allow me to provide another insight:
Hi @RRaj_293 ,
Here are the steps you can follow：
1. Create measure.
New Email Time =
MINX(
FILTER(ALL('Tbl1'),
'Tbl1'[GUID]=MAX('Tbl1'[GUID])&&'Tbl1'[PROCESS_STATUS]="NEW EMAIL"),[META_CREATE_DATE])Complete Status Time =
MINX(
FILTER(ALL('Tbl1'),
'Tbl1'[GUID]=MAX('Tbl1'[GUID])&&'Tbl1'[PROCESS_STATUS]="COMPLETE"),[META_CREATE_DATE])Processing Time =
CONCATENATE(MINUTE([Complete Status Time]-[New Email Time]) & " Min " , SECOND([Complete Status Time]-[New Email Time])& " Sec")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
Helpful resources
Power BI Monthly Update - August 2025
Check out the August 2025 Power BI update to learn about new features.
Fabric Community Update - August 2025
Find out what's new and trending in the Fabric community.
