what is the difference between the two given approaches on conversion of duration

[manoj_0911]

Hi everyone,

I'm seeking advice on the best approach in Power BI for handling call durations from IVR data. I have a dataset with 40+ seconds per call, and I need to achieve the following:

1. **Convert Seconds to HH:MM:SS Format:** I need to convert these durations into a readable format for display.
2. **Calculate Average Duration:** For some analyses, I want to calculate the average call duration.
3. **Data Visualization:** Finally, I need to display the data in various ways:
* Average duration in a box plot on the dashboard.
* Trend chart showing duration over time.
* Detailed table with date, division, category, individual durations, and average duration.

**Approaches:**

I've considered two approaches for converting seconds to HH:MM:SS:

**Approach 1 (Simpler):**

```
ACW Hours = INT([ACW_DURATION] / 3600)
ACW Minutes = INT(MOD([ACW_DURATION], 3600) / 60)
ACW Seconds = MOD([ACW_DURATION], 60)

ACW DurationFormatted = 
FORMAT([ACW Hours], "00") & ":" &
FORMAT([ACW Minutes], "00") & ":" &
FORMAT([ACW Seconds], "00")
```

**Approach 2 (More Complex):**

(This approach includes a long formula with logic for leading zeros and rounding)

APPROACH 2

acw Duration long formula =
VAR Duration = AGENT_SUBHOUR_V[ACW_DURATION]
// There are 3,600 seconds in an hour
VAR Hours =
INT ( Duration / 3600)
// There are 60 seconds in a minute
VAR Minutes =
INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours
VAR Seconds =
ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR H =
IF ( LEN ( Hours ) = 1,
CONCATENATE ( "0", Hours ),
CONCATENATE ( "", Hours )
)
// Minutes with leading zeros
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
)
// Seconds with leading zeros
VAR S =
IF (
LEN ( Seconds ) = 1,
CONCATENATE ( "0", Seconds ),
CONCATENATE ( "", Seconds )
)
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
CONCATENATE (
H,
CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
)

**Question:**

* Which approach is generally preferred for converting seconds to HH:MM:SS, considering clarity, efficiency, and compatibility?

**Calculating Average Duration:**

I understand I can use DAX functions like AVERAGE or SUMX to calculate the average duration based on specific criteria.

**Data Visualization:**

I'm familiar with creating Cards, trend charts, and detail tables in Power BI Desktop. However, if there are any specific recommendations for displaying this data effectively, I'd appreciate any insights.

**Additional Information:**

* My dataset includes fields like `Date`, `Name`, and `ACW_DURATION` (seconds).

Thanks in advance for your support!

Manoj P.

[v-junyant-msft · Accepted solution]

Hi @manoj_0911 ,

Between the two approaches you've outlined for converting seconds to the HH:MM:SS format, Approach 1 is generally preferred for its simplicity and efficiency. It's straightforward, easier to understand, and maintain. And after my test, it's right without any error.

For calculating the average call duration, using the AVERAGE DAX function is indeed a good approach. If you need to calculate the average based on specific criteria, consider using AVERAGEX along with a filter expression. I can give you some examples:
Here is my sample data:

If I want to calculate the total average:

Total = AVERAGEX(ALL('Table'), 'Table'[ACW_DURATION])

If I want to calculate the average by different Name:

_Name = CALCULATE(
    AVERAGE('Table'[ACW_DURATION]),
    ALLEXCEPT('Table', 'Table'[Name])
)

If I want to calculate the average value before 2024.3.1:

_Time = AVERAGEX(FILTER(ALL('Table'), 'Table'[Date] <= DATE(2024, 3, 1)), 'Table'[ACW_DURATION])

For the visualizations you mentioned, here are some recommendations:
A line chart would be ideal for showing trends over time. Use the Date field on the axis, and for values, you can use the average duration measure you created.
Use a Matrix visual for this purpose. Place Date, Division, and Category in the Rows area and your ACW_DURATION and average duration measure in the Values area. This will allow you to drill down from date to division to category while displaying individual and average durations.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.