cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vanessa250919
Helper III
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 ....

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
    
    
1 ACCEPTED SOLUTION
TarunSharma
Responsive Resident
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

View solution in original post

3 REPLIES 3
daxer
Solution Sage
Solution Sage

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
TarunSharma
Responsive Resident
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

View solution in original post

Hello,

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

Vanessa250919_0-1617863282934.png

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors