How to convert time duration to days, minutes, decimal numbers?

I have employee time tracking data, and I need to analyze and visualize work durations in different formats for reporting purposes. Any idea about how to transform duration data to time units?

Get time units from time duration in Power BI

This tutorial will guide you through the process of transforming duration data into various formats, enabling more flexible reporting and visualization options for your HR time tracking needs.

Assumed Data Model

We will assume that we have a table named EmployeeTimeTracking, that has the following fields:

• EmployeeID (Text)
• TaskName (Text)
• StartDateTime (DateTime)
• EndDateTime (DateTime)
• Duration (Duration)

1. Converting Time to Days

1. In Power BI Desktop, start by creating a new measure:

   Days = DATEDIFF(EmployeeTimeTracking[StartDateTime], EmployeeTimeTracking[EndDateTime], DAY)

2. In the Report tab, create a card visual and drag the “Days” measure into it.

2. Converting Duration to Minutes

1. Create a new measure:

   Minutes = DATEDIFF(EmployeeTimeTracking[StartDateTime], EmployeeTimeTracking[EndDateTime], MINUTE)

2. Add another card visual and use the “Minutes” measure.

3. Converting Time to Decimal Numbers (Hours)

1. Create a new measure:

   Hours Decimal = DATEDIFF(EmployeeTimeTracking[StartDateTime], EmployeeTimeTracking[EndDateTime], SECOND) / 3600

2. Format the measure to display 2 decimal places.
3. Add a card visual with the “Hours Decimal” measure.

4. Creating a Custom Format with DAX

1. Create a new measure:

   Custom Duration Format DAX = 
   VAR Days = INT(EmployeeTimeTracking[Duration])
   VAR Hours = INT(MOD(EmployeeTimeTracking[Duration] * 24, 24))
   VAR Minutes = INT(MOD(EmployeeTimeTracking[Duration] * 24 * 60, 60))
   RETURN
       FORMAT(Days, "0") & " days, " & 
       FORMAT(Hours, "0") & " hours, " & 
       FORMAT(Minutes, "0") & " minutes"

2. Add a table visual with EmployeeID, TaskName, and the new “Custom Duration Format DAX” measure.
3. To use this in a visual that allows measures:
   Create another measure that references the first one:

   Custom Duration Display = SELECTEDVALUE('EmployeeTimeTracking'[Custom Duration Format DAX])

4. Use the “Custom Duration Display” measure in card visuals or tables for a formatted duration display.

Here’s a short explanation about the formulas:

• This DAX formula breaks down the duration into days, hours, and minutes.
• INT function is used to get whole number parts.
• MOD function helps in extracting the remainder for hours and minutes.
• FORMAT function ensures consistent number formatting.
• SELECTEDVALUE is used in the second measure to display the formatted string in visuals that expect measures.

4. Creating a Custom Times using Power Query

We can also create a time format using Power Query – this saves the need to use DAX calculations:

1. Add a new column using Power Query:

   Custom Duration Format = 
   Text.From(Duration.Days([Duration])) & " days, " & 
   Text.From(Duration.Hours([Duration]) - Duration.Days([Duration]) * 24) & " hours, " & 
   Text.From(Duration.Minutes([Duration]) % 60) & " minutes"

2. Create a table visual with EmployeeID, TaskName, and the new “Custom Duration Format” column.

Few things to remember

• DATEDIFF function: Calculates the difference between two dates in specified units.
• Duration data type: Stores time intervals, requiring conversion for various display formats.
• Power Query: Useful for complex string manipulations and custom formatting.

What to do if this doesn’t work?

1. In case of incorrect results: Ensure StartDateTime is always before EndDateTime. Use an IF statement to swap dates if necessary.
2. Negative durations? Add error handling in measures, e.g., MAX(0, DATEDIFF(...)).
3. Formatting issues: You might want to check your regional settings time formats in Power BI and your source data.
4. I*f you encounter performance issues: For large sets of data, consider pre-calculating durations in your Power Query interface, instead of using DAX measures.