Forum Discussion
Time to Complete Survey
I have some attached data:
Start and End time for a survey. (Date/Time formatted)
Can someone help me write the DAX formula for total, avergage, Min and Max time to complete?
Let me know if you have any questions.
Hi Anonymous
With your data in the form it is, You can use iterators to do these calculations: SUMX, AVERAGEX, MINX, and MAXX.
With the below measures, I formatted them as HH:mm:ss
Total Time to Complete = SUMX ( Survey, Survey[End Time] - Survey[Start time] )
Average Time to Complete = VAR Result = AVERAGEX ( Survey, Survey[End Time] - Survey[Start time] ) RETURN IF ( NOT ISBLANK ( Result ), Result + TIME ( 0, 0, 0 ) ) // When datetime values are averaged, the result must be cast as datetime by adding a zero time valueMin Time to Complete = MINX ( Survey, Survey[End Time] - Survey[Start time] )
Max Time to Complete = MAXX( Survey, Survey[End Time] - Survey[Start time] )
You could also add a column to your table to pre-compute End Time - Start time, rather than calculating the duration of each row within the measure.
Regards,
Owen
2 Replies
Hi Anonymous
With your data in the form it is, You can use iterators to do these calculations: SUMX, AVERAGEX, MINX, and MAXX.
With the below measures, I formatted them as HH:mm:ss
Total Time to Complete = SUMX ( Survey, Survey[End Time] - Survey[Start time] )
Average Time to Complete = VAR Result = AVERAGEX ( Survey, Survey[End Time] - Survey[Start time] ) RETURN IF ( NOT ISBLANK ( Result ), Result + TIME ( 0, 0, 0 ) ) // When datetime values are averaged, the result must be cast as datetime by adding a zero time valueMin Time to Complete = MINX ( Survey, Survey[End Time] - Survey[Start time] )
Max Time to Complete = MAXX( Survey, Survey[End Time] - Survey[Start time] )
You could also add a column to your table to pre-compute End Time - Start time, rather than calculating the duration of each row within the measure.
Regards,
Owen