Forum Discussion
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 ...
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