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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
dubya
Regular Visitor

Update custom text column calculation to time type!

Hello, I am attempting to convert this decimal column into an hours and minutes column. 

 

My formula below works well, but creates the column as a text column not a number column. 

 

Adjusted Hours HHMM = VAR _hrs = QUOTIENT ( '12 1/2'[Average Adjusted Hours ] , 60 )
VAR _mins = INT ( '12 1/2'[Average Adjusted Hours ] - _hrs * 60 )
VAR _sec = MOD ( '12 1/2'[Average Adjusted Hours ] , 1.0 ) * 60
RETURN
FORMAT(_hrs,"00")&":"&FORMAT(_mins,"00")&":"&FORMAT(_sec,"00")  

 

I need to be able to sum these columns.

Capture.JPG

Thank you for your help!

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @dubya ,

I create a table as you mentioned.

vyilongmsft_0-1729043870947.png

Then I think you can change your DAX code, it will give you Date/Time type. You can change format as you like.

Column = 
VAR _hrs =
    QUOTIENT ( '12 1/2'[Average Adjusted Hours], 1 )
VAR _mins =
    MOD ( '12 1/2'[Average Adjusted Hours] * 60, 60 )
RETURN
    TIME ( _hrs, _mins, 0 )

vyilongmsft_1-1729044233498.png

Next I think you can create another calculated column.

Total Adjusted Hours =
VAR TotalMinutes =
    SUMX ( '12 1/2', '12 1/2'[Column] * 60 )
VAR Hours =
    QUOTIENT ( TotalMinutes, 60 )
VAR Minutes =
    MOD ( TotalMinutes, 60 )
RETURN
    TIME ( Hours, Minutes, 0 )

vyilongmsft_2-1729044392176.png

 

 

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
ThxAlot
Super User
Super User

Easy enough,

ThxAlot_0-1729025617936.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LearnAndPractise(Everyday)


)



lbendlin
Super User
Super User

If you want to be able to do math on these values then you need to keep them as is.  The format can be applied at the last second (ie as a different measure) before your display the result.

Thank you, 

 

Are you able to provide additional detail as to had to add the formula as a measure? 

Anonymous
Not applicable

Hi @dubya ,

I create a table as you mentioned.

vyilongmsft_0-1729043870947.png

Then I think you can change your DAX code, it will give you Date/Time type. You can change format as you like.

Column = 
VAR _hrs =
    QUOTIENT ( '12 1/2'[Average Adjusted Hours], 1 )
VAR _mins =
    MOD ( '12 1/2'[Average Adjusted Hours] * 60, 60 )
RETURN
    TIME ( _hrs, _mins, 0 )

vyilongmsft_1-1729044233498.png

Next I think you can create another calculated column.

Total Adjusted Hours =
VAR TotalMinutes =
    SUMX ( '12 1/2', '12 1/2'[Column] * 60 )
VAR Hours =
    QUOTIENT ( TotalMinutes, 60 )
VAR Minutes =
    MOD ( TotalMinutes, 60 )
RETURN
    TIME ( Hours, Minutes, 0 )

vyilongmsft_2-1729044392176.png

 

 

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

see the answer by @ThxAlot - even easier.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.