Daily DAX : Day 165 TIMEVALUE

 The TIMEVALUE function in Power BI's DAX (Data Analysis Expressions) language is used to convert a time represented as text into a time value (a decimal number between 0 and 1, where 0 represents 12:00:00 AM and 1 represents 11:59:59 PM). This function is particularly useful when you're working with time data stored as strings and need to perform time-based calculations or comparisons.

Syntax

TIMEVALUE(time_text)

    time_text: A text string that represents a time in a recognizable format, such as "2:30 PM" or "14:30". The string must follow a valid time format that Power BI can interpret.

Return Value

    A decimal number representing the time as a fraction of a day. For example:

        "12:00 AM" returns 0

        "12:00 PM" returns 0.5

        "11:59 PM" returns approximately 0.999988425925926

Key Points

    Input Format: The time_text argument must be a string in a time format that Power BI recognizes (e.g., "HH:MM:SS AM/PM" or "HH:MM" in 24-hour format). If the input is invalid, the function returns an error.

    No Date Component: TIMEVALUE only extracts the time portion and ignores any date information if present in the string.

    Output: The result is a numeric value that can be used in calculations or formatted as a time using Power BI's formatting options.

Use Case

The TIMEVALUE function is handy in scenarios where you need to:

    Convert text-based time data (e.g., from an imported dataset) into a format suitable for calculations.

    Perform comparisons or aggregations based on time values.

    Extract time from a mixed date-time string for specific analysis.

Example

Suppose you have a dataset with a column EventTime containing time values as text, such as "3:45 PM", "9:15 AM", etc. You want to calculate the duration between two events or filter records based on time.

    Convert Text to Time Value:

    Create a new calculated column in Power BI using DAX:

    TimeValueColumn = TIMEVALUE('Table'[EventTime])

        For "3:45 PM", this returns approximately 0.65625 (since 3:45 PM is 15 hours and 45 minutes out of 24 hours).

    Calculate Duration:

    If you have two columns, StartTime ("9:00 AM") and EndTime ("5:00 PM"), you can calculate the duration in hours:

    DurationHours = (TIMEVALUE('Table'[EndTime]) - TIMEVALUE('Table'[StartTime])) * 24

        TIMEVALUE("5:00 PM") = 0.708333 (17:00 in 24-hour format)

        TIMEVALUE("9:00 AM") = 0.375

        Difference = 0.708333 - 0.375 = 0.333333

        Multiply by 24 = 8 hours

    Filter or Conditional Logic:

    You can use TIMEVALUE in measures to filter data, e.g., to count events that occurred after 2:00 PM:

    EventsAfter2PM = CALCULATE(

        COUNTROWS('Table'),

        TIMEVALUE('Table'[EventTime]) > TIMEVALUE("2:00 PM")

    )

Practical Scenario

Imagine a call center dataset with a "Call Start Time" column stored as text (e.g., "10:15 AM"). You want to analyze average call durations or identify calls happening during peak hours (e.g., 1:00 PM to 5:00 PM). Using TIMEVALUE, you can convert these text entries into a numeric format, enabling you to perform arithmetic operations or time-based filters.

Limitations

    If the input string is not a valid time (e.g., "25:00" or "abc"), the function will return an error.

    It only works with time, not dates. For date-time combinations, consider using DATETIME or other functions like TIME.

In summary, TIMEVALUE is a simple yet powerful function for handling time-specific data in text form, making it a valuable tool for time-based analysis in Power BI.