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.
Hi all,
How can I calculate hours per week?
I have calculated hours per day
Hours per day = [starttime] - [endtime]
data type is 'Time'
I want to multiply this figure by 5 and minus 2.5h
if i do
Hours per week = ([Hours per day] * 5)-2.5
but it doesn't work
Solved! Go to Solution.
Hi @HenryJS ,
Try to create a column like so:
Column 2 =
VAR DateDiffSeconds =
DATEDIFF ( [StartTime], [EndTime], SECOND )
VAR Hours1 = DateDiffSeconds / 3600
VAR Hours2 = Hours1 * 5 - 2.5
VAR Hours =
TRUNC ( Hours2 )
VAR Minutes =
TRUNC ( ( Hours2 * 3600 - Hours * 3600 ) / 60 )
VAR Seconds = Hours2 * 3600 - Minutes * 60 - Hours * 3600
RETURN
Hours & ":"
& FORMAT ( Minutes, "00" ) & ":"
& FORMAT ( Seconds, "00" )
Then, you can convert the column type from "Text" to "Time". Or just change the expression like below and then choose datatype as "Time".
Column 3 =
VAR DateDiffSeconds =
DATEDIFF ( [StartTime], [EndTime], SECOND )
VAR Hours1 = DateDiffSeconds / 3600
VAR Hours2 = Hours1 * 5 - 2.5
VAR Hours =
TRUNC ( Hours2 )
VAR Minutes =
TRUNC ( ( Hours2 * 3600 - Hours * 3600 ) / 60 )
VAR Seconds = Hours2 * 3600 - Minutes * 60 - Hours * 3600
VAR Result =
Hours & ":"
& FORMAT ( Minutes, "00" ) & ":"
& FORMAT ( Seconds, "00" )
RETURN
CONVERT ( Result, DATETIME )
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @HenryJS ,
Try to create a column like so:
Column 2 =
VAR DateDiffSeconds =
DATEDIFF ( [StartTime], [EndTime], SECOND )
VAR Hours1 = DateDiffSeconds / 3600
VAR Hours2 = Hours1 * 5 - 2.5
VAR Hours =
TRUNC ( Hours2 )
VAR Minutes =
TRUNC ( ( Hours2 * 3600 - Hours * 3600 ) / 60 )
VAR Seconds = Hours2 * 3600 - Minutes * 60 - Hours * 3600
RETURN
Hours & ":"
& FORMAT ( Minutes, "00" ) & ":"
& FORMAT ( Seconds, "00" )
Then, you can convert the column type from "Text" to "Time". Or just change the expression like below and then choose datatype as "Time".
Column 3 =
VAR DateDiffSeconds =
DATEDIFF ( [StartTime], [EndTime], SECOND )
VAR Hours1 = DateDiffSeconds / 3600
VAR Hours2 = Hours1 * 5 - 2.5
VAR Hours =
TRUNC ( Hours2 )
VAR Minutes =
TRUNC ( ( Hours2 * 3600 - Hours * 3600 ) / 60 )
VAR Seconds = Hours2 * 3600 - Minutes * 60 - Hours * 3600
VAR Result =
Hours & ":"
& FORMAT ( Minutes, "00" ) & ":"
& FORMAT ( Seconds, "00" )
RETURN
CONVERT ( Result, DATETIME )
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@HenryJS ,Try like
Hours per Week= datediff( [starttime] ,[endtime],Hour)*5 -2.5
If you want to convert back to time
use
Total time = time([Hours per Week]),0,0)
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 |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |