Time Duration conversion

 

Step-by-Step DAX Query

1. Go to the Modeling tab in Power BI Desktop.

2. Click on "New Column" to create a new calculated column.

3. Enter the following DAX formula:

dax

FormattedDuration = 
VAR TotalSeconds = 'YourTable'[DurationInSeconds]
VAR Days = INT(TotalSeconds / 86400)
VAR Hours = INT(MOD(TotalSeconds, 86400) / 3600)
VAR Minutes = INT(MOD(TotalSeconds, 3600) / 60)
VAR Seconds = MOD(TotalSeconds, 60)
RETURN
IF(
    Days > 0,
    Days & " days " & Hours & " hours " & Minutes & " minutes " & Seconds & " seconds",
    IF(
        Hours > 0,
        Hours & " hours " & Minutes & " minutes " & Seconds & " seconds",
        IF(
            Minutes > 0,
            Minutes & " minutes " & Seconds & " seconds",
            Seconds & " seconds"
        )
    )
)

Explanation:

• TotalSeconds: Represents the original duration in seconds from your column.

• Days: Calculates the number of days by dividing the total seconds by 86400 (number of seconds in a day) and taking the integer part.

• Hours: Calculates the number of hours by taking the remainder of the total seconds after removing days (using the MOD function) and dividing by 3600 (number of seconds in an hour).

• Minutes: Calculates the number of minutes by taking the remainder of the total seconds after removing days and hours, and dividing by 60 (number of seconds in a minute).

• Seconds: Calculates the remaining seconds after removing days, hours, and minutes.

• RETURN: Constructs the formatted duration string based on the calculated values for days, hours, minutes, and seconds. It handles different scenarios where the duration may not include days, hours, or minutes.

This formula will create a new column called FormattedDuration that displays the time duration in a readable format, such as "2 days 3 hours 4 minutes 5 seconds" or "5 minutes 30 seconds."