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.
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
Solved! Go to 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
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
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
Hello,
First all thanks for your help & time !
The second query it's ok and perfect but for the one I have one issue
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 |
---|---|
41 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |