Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Rounding up time

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. 

Gediminas12_0-1603626220630.png

 

1 ACCEPTED 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 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

15 REPLIES 15
Fowmy
Super User
Super User

@Anonymous 

Time rounded as expected:

Rounded Time = 
IF( MINUTE([TIME]) >= 40,  
    MROUND([Time], 1/24 ),
    HOUR([Time])/24
)

Fowmy_0-1603629293949.png

________________________

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 🙂

YouTube  LinkedIn

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

@Fowmy @mahoneypat This is what i get: Might be the reason that Im using charlie eidens duration and displaying time as a whole number.

Gediminas12_0-1603630371892.png

 

@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 🙂

YouTube  LinkedIn 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

@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
)

 

 

Fowmy_0-1603631396724.png

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 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

@Fowmy  This is the error message that I get: 

Gediminas12_0-1603635566478.png

 

Anonymous
Not applicable

Or if i extract just the hour, I get zeros: 

Gediminas12_0-1603636005060.png

 

@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 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

@Fowmy Ive got it working now, however one of the drive time was 24:52 and when it rounds up it produces 1.

 

Gediminas12_0-1603641777087.png

 

@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 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

@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
)

v-lionel-msft_0-1603788227020.png

 

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.

 

Anonymous
Not applicable

@Fowmy  This does make sense, however, I need the column to be in the whole number format rather than time. 

Anonymous
Not applicable

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

mahoneypat
Employee
Employee

Please try a column expression like this to get your desired result.

 

Rounded Time = var hours = HOUR('Table'[Time])
var minutes = MINUTE('Table'[Time])
return IF(minutes>=40, hours +1, hours)
 
Regards,
Pat
 




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.