Convert Duration into DD:HH:MM

[michael_knight]

Hi,

I'm trying to convert a Duration field, display it by DD:HH:MM. Using this article I was able to yield these result

Which is alright if I want to display it in a table, but I want to display it in a Column Chart. When I try and put the measure in the column chart it automatically going into the Tooltips and I'm unable is usualise it

https://www.dropbox.com/s/raye0i6xfvr0h9y/dates.pbix?dl=0

Does anyone have any suggestions on how I can visualise this?

Cheers,

Mike

[v-xulin-mstf]

Hi @michael_knight,

It is not support to put TXET formatted fields or non-aggregated values as values in a bar chart in power bi now.

Maybe you can summit a idea here.

Best Regards,

Link

[michael_knight]

Hi Link,

Is there no alternatives options I can have? I have two date columns, surely there must be a way to create a DD:HH measure at least?

Cheers

[v-xulin-mstf]

Hi @michael_knight,

Sorry, it is a great pity that the bar chart does not support non-aggregated values in text format as value.

Even DD:HH is in text format.

Best Regards,

Link

[michael_knight]

Hi,

Still looking for suggestion if anyone has any!

Cheers

[amitchandak]

@michael_knight , In power query you can use duartion

[creationon] - [new_datelastssigned]

in DAX

duration = [creationon] - [new_datelastssigned] // but this will give dd hh:mm:ss

duration1 = day([duration]) & ":" &  hour([duration]) & ":" &  hour([Minutes])  & ":" &  hour([Second]) 

[michael_knight]

Managed to get the DAX to work but it still doesn't come up with what I want it to. 

I want it to show DD:HH:MM:SS, and this isn't doing it.

Format = var dur = Opportunity[new_datelastassigned] - Opportunity[createdon]
var minu = MINUTE(dur)
var sec = SECOND(dur)
return  
day(dur) & ":" &  hour(dur)  & ":" &  hour(minu)  & ":" &  hour(sec) 

Also, for some reason the days always max out at 30, even though the difference could be 60 days for example

[michael_knight]

Hi,

Sorry I didn't make myself clear, I used that formula in Power Query and then used this DAX code to get the outcome

CreatedOn to DateLastAssigned 1 = 

//1.878472
VAR Elapsed_Time = SELECTEDVALUE(Sheet1[CreatedOn to DateLastAssigned])


VAR days = INT (Elapsed_Time) //1

// (1.878472 - 1) *24 =21.083328
VAR _hrs = (Elapsed_Time - days) *24

VAR hrs = INT(_hrs) //21

// ROUND( (21.083328 - 21) * 60, 0) = ROUND( 4.99968 , 0) = 5
VAR mins = ROUND ((_hrs - hrs) * 60,0)

RETURN

//Use FORMAT to FORMAT the string to use 2 chars e.g. 03 rather than just 3
days & " d " & FORMAT(hrs,"00") & " h " & FORMAT(mins,"00") & " m "

Tried to do what you suggested in DAX and it didn't work