Change a decimal number to hours & minutes

[Anonymous]

Hi

I am trying to work out how much CPD each staff member. I have imported trhe data as a decimal figure and used a simple measure to change this to a total hours as below:

Measure 2 = CALCULATE( SUM( 'Completed CPD (2)'[Duration (Hours) copy]) * 24)

This give me the result as 0.25, 139.0, 16.75, 5.50 etc. Can this be changed so that the format is 0:15, 139:00, 16:45 and 5:30? 

I have tried the following:

sumx('Completed CPD (2)', hour('Completed CPD (2)'[Duration (Hours) copy])) + TRUNC(sumx('Completed CPD (2)', MINUTE('Completed CPD (2)'[Duration (Hours) copy]))/60)  & ":" & mod(sumx('Completed CPD (2)', MINUTE('Completed CPD (2)'[Duration (Hours) copy])), 60)

This works for any hours below 24 hours but when the time is 45 hours it shows it as 21:00 (i.e. deducting the first 24 hours).

Any help would be really appreciated.

Thanks

Holly 

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may refer to below measure:

Format =
VAR a =
    ( MAX ( Table[Column1] ) - TRUNC ( MAX ( Table[Column1] ), 0 ) )
        * 60
VAR b =
    INT ( MAX ( Table[Column1] ) )
RETURN
    CONCATENATE ( b, CONCATENATE ( ":", a ) )

Regards,

Cherie

[Anonymous]

This works well in solving my similar problem but now i get a table value that has several decimal places to the seconds value?

I have tried to look at format options but this is not available to me for the measure.

Can you help?

[Greg_Deckler]

Check out this post:

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486