Time to Complete Survey

[Anonymous]

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.

[OwenAuger · Accepted solution]

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 value

Min 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

[Anonymous]

Thank you so much! Very helpful (especially the Average time)