From Decimal Hours Back to Conventional Time: Dynamic Formatting in DAX

In yesterday’s blog post, I explained how to use Power Query to convert conventional time formats like „25:15“ into decimal hours, making them easier to calculate within Power BI. Today, I’d like to share an idea for using DAX’s dynamic formatting to display these decimal hour values back in a conventional time format.

Why Does This Matter?

Decimal hours are efficient for calculations but can be unfamiliar and harder to interpret for many users. For example, „2.5 hours“ is equivalent to „2 hours and 30 minutes.“ Displaying the result in a familiar time format enhances readability and makes your Power BI reports more accessible.

Dynamic Formatting: The Key to Flexible Displays

DAX measures allow dynamic formatting with the FORMAT function, enabling us to transform decimal hour values into a more understandable „hh:mm“ format.

DAX Code to Generate the Formatting String

Here’s the DAX code to create a dynamic formatting string that converts decimal hours into a „hh:mm“ time format:

VAR _decimalHours = SELECTEDMEASURE()
VAR _Minutes = MOD(_decimalHours, 1)
VAR _TimeString = FORMAT(_decimalHours - _Minutes + 0.6 * _Minutes, "#.00", "en-US")
VAR _FmtString =
    SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
        _TimeString, "0", "\0"), "1", "\1"), "2", "\2"), "3", "\3"), "4", "\4"), "5", "\5"),
        "6", "\6"), "7", "\7"), "8", "\8"), "9", "\9"), ".", "\:")
RETURN
    _FmtString

How the Code Works

1. Calculating the Minute Component:
   • MOD(_decimalHours, 1) extracts the fractional part of the hour.
   • This fractional part is multiplied by 0.6 to convert it to minutes (e.g., 0.5 hours = 30 minutes).
2. Formatting into a String:
   • The value is formatted into a numeric string with two decimal places using "#.00".
3. Transforming into a Time Format:
   • The SUBSTITUTE function replaces each digit with its escaped equivalent (e.g., „0“ becomes \0) and the decimal point with a colon to form a time string.
4. Returning the Result:
   • The constructed time format string is returned.

Implementing in Power BI

To use this logic in Power BI, simply create a measure using the above DAX code. Combined with dynamic formatting, the output will automatically display in the desired time format based on the input or context.

Conclusion

Using dynamic formatting and DAX logic, you can make your Power BI reports both functional and user-friendly. This approach not only simplifies complex data presentations but also enhances readability by transforming decimal hour values back into a standard time format.

Give it a try and let me know how it works for you! 😊