Transform date to duration or whole number in minutes to duration

[Elissa123]

Hi community,

I'm quite new to this amazing world of power BI and in desperate need of your help.

I want to calculate the average duration of an sla result comming from servicenow. The problem is that the duration can be over 24 hours. This is how the data is displayed on load.

As you can see, it's a date value. It doesn't allow me to convert this to duration using power query. I've tried to apply this https://community.powerbi.com/t5/Desktop/convert-minutes-into-hours-minutes-and-seconds-Not-Days/m-p...

But it gets me a text field and I need a duration type of field because I need that for a card that I want to filter dinamically with other graphs.

I've also managed to convert the whole duration to a whole number type of field where i have this duration in minutes. When trying to set it as duration, it just retrieves the number (minutes) as if they where days.

Is there a way to convert this whole number that is minutes into a duration? Or directly convert the original data field?

Please assist, this is hunting me at nights! lol

Thanks!

[PhilipTreacy]

Hi @Elissa123 

Download updated example PBIX file

Power Query has a duration data type but Power BI (DAX) does not.  So when you load the data from PQ into the model, that's why you are seeing the durations as a decimal.  Yeah, I know, 2 systems that talk to each other in the same program don't support the same data types.

Anyway, once you have the data loaded into the model and ready to display it, you have to manually format the decimal duration to display as you want.

I've written about doing this here

Convert decimal time to days, hours, minutes in Power BI

Modifying the output format slightly to suit your need, I've come up with this

Duration Formatted = 

VAR Elapsed_Time = SELECTEDVALUE('DataTable'[Duration])
VAR days = INT(Elapsed_Time)

VAR _hrs = (Elapsed_Time - days) * 24
VAR hrs = INT(_hrs)

VAR _mins = ROUNDUP((_hrs - hrs) * 60, 2)
VAR mins = INT(_mins)

VAR seconds = INT((_mins - mins) * 60)

RETURN

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

Hopefully you can integrate that into your own file. Post back if you need a hand.

regards

Phil

[ppm1]

Looks like you can create a duration column by subtracting #datetime(1970,1,1,0,0,0) from your business_duration column and convert it to type duration. When you load that duration column, it will convert to a decimal column (in days). From there you can use the approach in this article to display it as a duration as needed.

Calculate and Format Durations in DAX – Hoosier BI

Pat

[PhilipTreacy]

Hi @Elissa123 

Download example PBIX file

There's a few intermediate type conversions involved, and you have to split the business_duration column into separate day and h:m:s components, but you can do it using this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDRNzDUN7Q0N1CwsDIAIlOlWJ1oJUNDfQMjmLCxgZUJRNjAGK7a3MrY0srQWCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [business_duration = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "business_duration", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"business_duration.1", "business_duration.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"business_duration.1", type date}, {"business_duration.2", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [business_duration.1] - #date(1970,1,1)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"business_duration.2", type number}, {"Custom", type duration}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"business_duration.2", type duration}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Duration", each [business_duration.2] + [Custom]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"Duration", type duration}})
in
    #"Changed Type3"

Regards

Phil

[Elissa123]

Hi @PhilipTreacy ,

Thanks a lot for this, although I still can't build a visual on it. I have managed to build it in the query editor but, when returning to the data view it's a decimal number.

So, I can't really calculate the average and display it as a duration 😞

My final goal is to have something like this: 

In the left card what I'd like to have (data is wrong) and in the right, what I get using the duration you helped me with. The goal is for this measure to be dynamic when clicking in the map.

Is there any way to resolve this?

Thank you soo much for your help!