Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey guys, I've got the following situation below and basically in the column where the time is, I need to round it up to only hours, if its above 40 minutes. So lets say its 15:30 then it displays 15, but if its 15:40 then it rounds up to 16 hours. Any way to achieve that by adjusting the code?
Any help appreciated.
Solved! Go to Solution.
@Anonymous
You need to use the seconds as input for my formula, it converts it into time.
I have attached your file with the column added.
You can download the file: HERE
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Time rounded as expected:
Rounded Time =
IF( MINUTE([TIME]) >= 40,
MROUND([Time], 1/24 ),
HOUR([Time])/24
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy @mahoneypat This is what i get: Might be the reason that Im using charlie eidens duration and displaying time as a whole number.
@Anonymous
My formula is based on Time data type column, if you have any other type, I need to do it differently.
You may share a sample PBIX file to check.
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy @mahoneypat You will find my pbix file here: https://gofile.io/d/Vsei2l The column name is NewTIme
@Anonymous
I modified the formula, you can try now:
Rounded Time =
VAR T = TIME(0,0,[Time]) RETURN
IF( MINUTE(T) >= 40,
MROUND(T, 1/24 ),
HOUR(T)/24
)
If you want to extract the hour as a number then apply the below formula and format it as the Whole Number
Rounded Time =
VAR T = TIME(0,0,[Time]) RETURN
HOUR(
IF( MINUTE(T) >= 40,
MROUND(T, 1/24 ),
HOUR(T)/24
)
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Or if i extract just the hour, I get zeros:
@Anonymous
You need to use the seconds as input for my formula, it converts it into time.
I have attached your file with the column added.
You can download the file: HERE
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy Ive got it working now, however one of the drive time was 24:52 and when it rounds up it produces 1.
@Anonymous
That is correct, 24 is midnight, 24:57 is next day 1am when rounded up.
hope this clarifies
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy Yes that does make sense, however I would need to display 25, as in my vizualisations im displaying hours, any way to adjust the code?
Hi @Anonymous ,
Try this formula.
Column =
VAR x = FORMAT( [Trukmė Prastova Radviliškis Convert], "00:00")
VAR __right = RIGHT(x, 2)
VAR __left = LEFT(x, 2)
RETURN
IF(
__right >= "40",
__left+ 1,
__left-0
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Fowmy This does make sense, however, I need the column to be in the whole number format rather than time.
This is the code I use for my time.
Trukmė Prastova Radviliškis Convert = VAR Duration = SUM ( 'Avg By Day'[avg_time] ) // There are 3,600 seconds in an hour VAR Hours = INT ( 'Avg By Day'[avg_time] / 3600 ) // There are 60 seconds in a minute VAR Minutes = INT ( MOD ( 'Avg By Day'[avg_time] - ( Hours * 3600 ), 3600 ) / 60 ) VAR Seconds = ROUNDUP ( MOD ( MOD ( 'Avg By Day'[avg_time] - ( Hours * 3600 ), 3600 ), 60 ), 0 ) RETURN Hours * 100 + Minutes
Please try a column expression like this to get your desired result.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |