Formatting Time and Null Time

[cdizzle22]

Hello Community,

I am using Power BI to measure the time a ticket was opened using some cold data. The time is built off of when it was put into a resolved state minus when it was opened. The calculation comes back correct but in a decimal format. Does anyone have pointers on how to format this decimal into Days, Hours, Minutes?

Also, for tickets that are still open the value in resolved time is null so it returns a gross negative number. Does anyone have pointers on how to treat these null values and possibly replace them with a current date that continuously updates?

Thanks!

[v-jingzhang · Accepted solution]

Hi @cdizzle22 

You can replace null with the current datetime with the following code. Replace "previous step name" with your last step name in Power Query Editor. 

= Table.ReplaceValue(#"previous step name",null,DateTime.LocalNow(),Replacer.ReplaceValue,{"Resolved Time"})

Then create a custom column with [Resolved Time] - [Open Time] to get the duration. 

Add another custom column to format the Duration values. The new Duration column will be Text type. 

= Table.AddColumn(#"Added Custom", "Duration new", each Text.From(Duration.Days([Duration])) & "Days," & Text.From(Duration.Hours([Duration])) & "Hours," & Text.From(Duration.Minutes([Duration])) & "Minutes")

If you want to convert format with DAX method at report side, you can refer to the following blogs:

Converting Decimal Time to Days, Hours, Minutes, Seconds in Power BI • My Online Training Hub

Calculate Duration in Days Hours Minutes and Seconds Dynamically in Power BI using DAX - RADACAD

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[artemus]

If you are using the duration data type to do the calculations, you need to add a new column with the value converted to text first. Then once loaded into the model, you need to set the sort column of the text column with the duration to that of the decimal column.

If you are only working in decimals in Power Query, you can use a custom column with:

[Ticket_Open_Time] * #duration(1, 0, 0, 0) 

assuming that [Ticket_Open_Time] is the column name that contains the decimal value.