cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Dobby_Libr3 Regular Visitor
Regular Visitor

Changing decimal number into Duration

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.

 

Core Time - M Calc.PNGM Query CalculationQuery Editor.PNGQuery Editor View           Desktop.PNGDesktop 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 

8 REPLIES 8
mussaenda New Contributor
New Contributor

Re: Changing decimal number into Duration

hi @Dobby_Libr3 ,

 

Use

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

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

Dobby_Libr3 Regular Visitor
Regular Visitor

Re: Changing decimal number into Duration

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. 

 

Error.PNGQuery Error.PNG

 

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

Super User IV
Super User IV

Re: Changing decimal number into Duration

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Community Support Team
Community Support Team

Re: Changing decimal number into Duration

Hi @Dobby_Libr3 ,

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 New Contributor
New Contributor

Re: Changing decimal number into Duration

Hi @Dobby_Libr3 ,

 

2019_10_08_08_44_58_Window.png

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

2019_10_08_09_29_31_Untitled_Power_BI_Desktop.png

 

 

 

Community Support Team
Community Support Team

Re: Changing decimal number into Duration

Hi @Dobby_Libr3 ,

 

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
Dobby_Libr3 Regular Visitor
Regular Visitor

Re: Changing decimal number into Duration

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

Community Support Team
Community Support Team

Re: Changing decimal number into Duration

Hi @Dobby_Libr3 ,

 

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.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors