Changing decimal number into Duration

[Anonymous]

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 

[Icey]

Hi @Anonymous ,

Is this problem sloved?

If it is sloved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards
Icey

[Anonymous]

Hi @Icey, 

This is still ongoing unfortunantly!  when I tired to use Gregs Dax code I'm getting a insuffient RAM message, My laptop has 32GB and Corei7 8th Gen Processor. 

When I get the issues sorted I'll mark the soultion!

Thanks 

Dobby

[Icey]

Hi @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. 

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: Time Duration and Time Graph Plotting.

Best Regards,
Icey

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

[Icey]

Hi @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. 

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: Time Duration and Time Graph Plotting.

Best Regards,
Icey

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

[mussaenda]

hi @Anonymous ,

Use

Duration.TotalSeconds([TT_Finished] - [TT_Started])

so your duration will be in seconds then you can follow the post from @Greg_Deckler 

[Anonymous]

Hi @mussaenda, 

Thanks for getting back to me! Unfortuantly that didn't work! I got an error where some of the Finished dates return blanks. 

I tried to use the Try  Otherwise function. But this just returned the "otherwise" value for all rows.

[mussaenda]

Hi @Anonymous ,

As you can see, I used an If Else for the null then it's okay.

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

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") 
        & ":"
        & FORMAT(minutes, "00")
        & ":"
        & FORMAT(seconds, "00")

[Greg_Deckler]

@Anonymous  - 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:

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.