Changing decimal number into Duration

Question

I have a table with a Start and Finish time I have created a calculated column which subtracts Finish time from start time to give me a duration. The issue I'm having is the format in which the Duration is displayed in desk top.

My Start and Finish times are in the follow format (DD/MM/YYYY HH:MM:SS). After the Calculation in the query editor the result is shown as a duration format. When pushed into desktop its then changed to a decimal number. I tired keeping the results as text - that didn't work.

M Query CalculationQuery Editor View Desktop View

I have seen the duration post by Greg_Deckler and itsfantastic - I just can't get them to work as my Core time column isn't in seconds. Any assistance would be greatly appricated.

Thanks

Dobby

mussaenda · Answer

hi Anonymous ,&nbsp;UseDuration.TotalSeconds([TT_Finished] - [TT_Started])so your duration will be in seconds then you can follow the post from Greg_Deckler&nbsp;

anonymous · Answer

Hi&nbsp;mussaenda,&nbsp;&nbsp;Thanks for getting back to me! Unfortuantly that didn't work! I got an error where some of the Finished dates return blanks.&nbsp;&nbsp;&nbsp;I tried to use the Try&nbsp; Otherwise function. But this just returned the "otherwise" value for all rows.

greg_deckler · Answer

Anonymous&nbsp; - It looks like you are getting back a value that is essentially the decimal portion of a day (24 hours). In DAX, you could get that duration in seconds using the following:
&nbsp;
Column = ([Finished] - [Started]) * 1. * 24 * 60 * 60
So, Finished - Started and you multiply by 1. to get a decimal value. You then multiply this by 24 to get the number of hours, 60 to get the number of minutes and then 60 again to get the number of seconds.

icey · Answer

Hi&nbsp;Anonymous ,Based on my knowledge, Power BI doesn't support to display Duration type in Power BI Report View as in Power Query.You can vote this idea to make it come soon.&nbsp;If you must display the duration time in the report view, there is one workround. You can copy the column and set it to text format and place it on the Tooltip. You can refer to this post:&nbsp;Time Duration and Time Graph Plotting.&nbsp;Best Regards,Icey&nbsp;If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

mussaenda · Answer

Hi Anonymous ,&nbsp;As you can see, I used an If Else for the null then it's okay.&nbsp;Then in Dax, I transformed it to duration time format which I also found here in the forum.Core Time_Seconds Measure = SUM('Table'[Core Time_Seconds])&nbsp;Duration Format Time_Employee Measure = 
 VAR hours = 
    ROUNDDOWN ( [Core Time_Seconds Measure] / 3600, 0 )
VAR minutes =
    ROUNDDOWN ( MOD ( [Core Time_Seconds Measure] , 3600 ) / 60, 0 )
VAR seconds =
    INT ( MOD ( [Core Time_Seconds Measure] , 60 ) )
RETURN
        FORMAT(hours,"00") 
        &amp; ":"
        &amp; FORMAT(minutes, "00")
        &amp; ":"
        &amp; FORMAT(seconds, "00")&nbsp;&nbsp;&nbsp;

Forum Discussion

Changing decimal number into Duration

8 Replies

Related content

change duration format to decimal

Duration keeps changing to decimal after load

Change decimal to duration

decimal to duration conversion

Decimal Precision