cancel
Showing results for
Did you mean:
Helper III

Dax measure avg time

Hello All,

I'm replacing a PowerBI consultant and I'm having trouble understanding his formula, can someone help me? Why in the result do * 1000 etc ....

Vanessa

_AVG_duration =

VAR c_Tm = AVERAGE(LAST_HOURS_RONDE[CLOSURE_END])

VAR s_Tm = AVERAGE(LAST_HOURS_RONDE[PERFORM_START])

VAR diff = DATEDIFF(s_Tm,c_Tm,SECOND)
VAR d_sec = mod(diff,60)
VAR d_mn = mod((diff-d_sec)/60,60)
VAR d_hr = mod((diff - d_mn * 60 - d_sec)/60/60,24)

Return

(d_hr*10000+d_Mn*100/60*100+d_sec/60*100)/10000

1 ACCEPTED SOLUTION
Responsive Resident

Hello @daxer ,
Instead of creating another variable SecondsPerHour, we can easily write in another way.

[_avg_duration] =
var AvgClosureEnd = AVERAGE( T[closure_end] )
var AvgPerformStart = average( T[perform_stat] )
var DifferenceInSeconds =
DATEDIFF(
AvgPerformStart,
AvgClosureEnd,
second
) / 3600
return
DifferenceInSeconds

OR

[_avg_duration] =
var AvgClosureEnd = AVERAGE( T[closure_end] )
var AvgPerformStart = average( T[perform_stat] )
var DifferenceInSeconds =
DIVIDE(
DATEDIFF(
AvgPerformStart,
AvgClosureEnd,
second
),
3600
)
return
DifferenceInSeconds

3 REPLIES 3
Solution Sage

First of all, I have to tell you I would never employ a consultant that would write such ugly and foggy code; such consultants are total rubbish and employing them is shooting onself in the foot.

Second of all, this code calculates the number of hours (as a float) between s_Tm and c_Tm.

Here is what it should look like:

``````// T is your table

[_avg_duration] =
var SecondsPerMinute = 60
var MinutesPerHour = 60
var SecondsPerHour = MinutesPerHour * SecondsPerMinute
var AvgClosureEnd = AVERAGE( T[closure_end] )
var AvgPerformStart = average( T[perform_stat] )
var DifferenceInSeconds =
DATEDIFF(
AvgPerformStart,
AvgClosureEnd,
second
)
var HoursInDifference =
int( DifferenceInSeconds / SecondsPerHour )
var MinutesInDifference =
int(
divide(
DifferenceInSeconds
- HoursInDifference * SecondsPerHour,
SecondsPerMinute
)
)
var RemainingSeconds =
DifferenceInSeconds
- HoursInDifference * SecondsPerHour
- MinutesInDifference * SecondsPerMinute
var TimeInHoursAsFloat =
HoursInDifference
+ MinutesInDifference / MinutesPerHour
+ SecondsInDifference / SecondsPerHour
return
TimeInHoursAsFloat``````

The code, even though longer, is clearer and easily understandable due to the names of the variables.

Would you agree?

And here is an even better version...

``````[_avg_duration] =
var AvgClosureEnd = AVERAGE( T[closure_end] )
var AvgPerformStart = average( T[perform_stat] )
var DifferenceInSeconds =
DATEDIFF(
AvgPerformStart,
AvgClosureEnd,
second
)
var SecondsPerHour = 60 * 60
return
DifferenceInSeconds / SecondsPerHour``````
Responsive Resident

Hello @daxer ,
Instead of creating another variable SecondsPerHour, we can easily write in another way.

[_avg_duration] =
var AvgClosureEnd = AVERAGE( T[closure_end] )
var AvgPerformStart = average( T[perform_stat] )
var DifferenceInSeconds =
DATEDIFF(
AvgPerformStart,
AvgClosureEnd,
second
) / 3600
return
DifferenceInSeconds

OR

[_avg_duration] =
var AvgClosureEnd = AVERAGE( T[closure_end] )
var AvgPerformStart = average( T[perform_stat] )
var DifferenceInSeconds =
DIVIDE(
DATEDIFF(
AvgPerformStart,
AvgClosureEnd,
second
),
3600
)
return
DifferenceInSeconds

Helper III

Hello,

First all thanks for your help & time !
The second query it's ok and perfect but for the one I have one issue

Announcements