Forum Discussion
Dax Help - Incorrect difference in time between 2 values of same datetime column
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
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
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 -
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" .
Please help! How do I find the correct average and display it in HH:MM:SS format?
| CustomerName | GUID | Test_ProcessTime | Test_Totlprocessing TimeinSec |
| ABC | 00272fd1 | 4 Min 58 Sec | 298 |
| ABC | 02a8b18b | 17 Min 59 Sec | 1079 |
| ABC | 035b8aa0 | Incomplete Process | 0 |
| ABC | 075ddfa8 | Incomplete Process | 0 |
| ABC | 0bba233d | 2 Min 33 Sec | 153 |
| XYZ | 1307af71 | 3 Min 24 Sec | 204 |
| XYZ | 148fd7bd | 2 Min 8 Sec | 128 |
| XYZ | 1655b305 | 5 Min 18 Sec | 318 |
| XYZ | 1be32838 | 54 Min 13 Sec | 3253 |
| XYZ | 1c3ab04e | 5 Min 11 Sec | 311 |
Your sample data is not covering your scenario. None of the averages exceed 60 minutes.
