Issues Formatting for Duration

[ccotton]

Power BI Novice in need of figuring out how I can get a Jira import for Time to First Response to report as a duration and not text and a total count.

I've tried a replace text option doing replace "h " with : and then "m " with ":00" just to try to get me in the right direction but then I get errors for the ons that show only minutes or the negative values. Is there a method I'm missing here? Almost all of the other fields show in seconds or milliseconds or even date and time, just not this field.

My end result is needed to know the average First Response for my report which I'm able to filter by day, month, year.

Thanks in advance. 

[rsbin · Accepted solution]

@ccotton ,

I would approach your issue in the following manner:

First, either in Power Query or DAX, convert your Time to First Reponse to Seconds.  Then I use the following:

WorkItemDuration_Format = 
VAR days = 
    ROUNDDOWN([WorkItemDuration] / 86400, 0 )
VAR hours =
    ROUNDDOWN ( MOD ( [WorkItemDuration], 86400) / 3600, 0 )
VAR minutes =
    ROUNDDOWN ( MOD ( [WorkItemDuration], 3600 ) / 60, 0 )
VAR seconds =
    INT ( MOD ( [WorkItemDuration], 60 ) )
RETURN
    FORMAT(days,"00") & " days "
        & FORMAT(hours,"00") & ":"
        & FORMAT(minutes, "00")
        & ":"
        & FORMAT(seconds, "00")

You can make adjustments (like commenting out the Days) if this time frame is not needed.

Hope you can make this work for your situation.

Regards,