Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Thank you so much! Very helpful (especially the Average time)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |