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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
Anonymous
Not applicable

How to convert decimal time to hh:mm and summarize that column?

Hi,

 

I need to display for every person how long she worked each day and I need to display the summarized hours of all persons. It should be displayed in the format hh:mm.

Therefore I use a measure to convert the decimal time [IST] to minutes and another measure to convert from minutes to hh:mm.

minutes = TRUNC([IST])*60+(([IST])-Trunc([IST]))*60

hh:mm = [minutes]/60/24

 

It looks like this:

TimeDecimal-to-hhmm.png

I don't really need to display the display the colums Ist and minutes.

I only need to display the column hh:mm and the total sum below.

How could I achieve that?

 

Many thanks for hints.

 

1 ACCEPTED SOLUTION
speedramps
Community Champion
Community Champion

Hi Peterman

Don't use time units to diplay aggregated time, because they cant cope with more that 24hrs.

...

Then display results in visuals as text (eg display 65.5 mins  =  01:05:30)

I have taken trouble to help you, now please help me by giving kudos.
Please click the thumbs up if you like me trying to help you and click Accept as Solution if I have fixed you problem.

Thanks.

 

Total duration in MM:SS =

-- convert decimal duration to hh:mm:ss text  
-- eg converts  65.5 to  "01:05:30"   and converts 0 to  00:00

 

-- Take decimal minutes as input eg 5.5 minutes

VAR DecimalMinutes = AVERAGE(Calls[Conversation duration])

-- Convert decimal minutes to seconds

VAR Duration = DecimalMinutes * 60

-- Calc the days, hours, minutes and seconds  (add days if required

VAR Hours = INT(DIVIDE(Duration,3600,0))

VAR Minutes = INT(DIVIDE(MOD( Duration - ( Hours * 3600 ),3600),60,0))

VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)

-- Format hours, minutes and seconds to two decimals

VAR H = IF (LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ))

VAR M = IF (LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ))

VAR S = IF (LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ))

 

RETURN

-- if input is 0 return 00:00.

-- return hours:mins:seconds as text

-- handles any errors(unlikely)

-- if input is greater than 24hrs then the hours will be displayed

IFERROR(

    IF (DecimalMinutes>0,

    H & ":" & M & ":" & S,

    "0:00"),

    "Error")

View solution in original post

5 REPLIES 5
speedramps
Community Champion
Community Champion

Hi Peterman

Don't use time units to diplay aggregated time, because they cant cope with more that 24hrs.

...

Then display results in visuals as text (eg display 65.5 mins  =  01:05:30)

I have taken trouble to help you, now please help me by giving kudos.
Please click the thumbs up if you like me trying to help you and click Accept as Solution if I have fixed you problem.

Thanks.

 

Total duration in MM:SS =

-- convert decimal duration to hh:mm:ss text  
-- eg converts  65.5 to  "01:05:30"   and converts 0 to  00:00

 

-- Take decimal minutes as input eg 5.5 minutes

VAR DecimalMinutes = AVERAGE(Calls[Conversation duration])

-- Convert decimal minutes to seconds

VAR Duration = DecimalMinutes * 60

-- Calc the days, hours, minutes and seconds  (add days if required

VAR Hours = INT(DIVIDE(Duration,3600,0))

VAR Minutes = INT(DIVIDE(MOD( Duration - ( Hours * 3600 ),3600),60,0))

VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)

-- Format hours, minutes and seconds to two decimals

VAR H = IF (LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ))

VAR M = IF (LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ))

VAR S = IF (LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ))

 

RETURN

-- if input is 0 return 00:00.

-- return hours:mins:seconds as text

-- handles any errors(unlikely)

-- if input is greater than 24hrs then the hours will be displayed

IFERROR(

    IF (DecimalMinutes>0,

    H & ":" & M & ":" & S,

    "0:00"),

    "Error")

Anonymous
Not applicable

You could just do

 

hh:mm = TIME(0, [IST]*60, 0)

--Nate

Anonymous
Not applicable

Thank you, it works partially. I see the values as expected. How do I get the values summed up?

I could select Do not Summarize, Count or Count distinct from Default Summarization. There are no options to select any further items like Sum.

CNENFRNL
Community Champion
Community Champion

CNENFRNL_0-1651431194319.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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