Dax measure avg time

[Vanessa250919]

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 ....

Thanks for your help.

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

[Anonymous]

Hi @Vanessa250919 

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

[Anonymous · Accepted solution]

Hello @Anonymous ,
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

[Vanessa250919]

Hello,

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