How to calculate average resolution time in DAX: step by step

João Barros 11 de August de 2026 4 min read

This tutorial shows how to calculate the average resolution time (in hours or days) for tickets in DAX — useful to evaluate SLA and support performance. We will explain why to use measures instead of calculated columns and give a practical example with variables, handling of missing values and suggestions to validate the results in the model.

Prerequisites

Power BI Desktop or another client that supports DAX.
Model with a fact table named Tickets containing the columns code TicketID, code CreatedDate, code ResolvedDate and code Status.
Date table Date with column code Date marked as Date Table. Having a Date Table allows filtering by month, quarter and correctly calculating running totals.
Example data volume: if you have 10,000 tickets, the measures below will be fast; with 1,000,000 rows you should test performance and use aggregations.

Step 1: Understand why to use a measure and not a column

A calculated column generates a value per row at refresh time and increases model size. For example, an additional column with a number that occupies 8 to 16 bytes per row in a table of 1,000,000 rows can consume an extra 8 to 16 MB, plus more in metadata. Also, a column is static and does not react to the filter context in a visual.

A measure calculates dynamically in the visual context. For average resolution time we want flexibility: filter by period, team, priority or status and see how the average changes. That’s why we use measures — less storage and more flexibility.

Step 2: Calculate the basic time difference (hours)

We will create a measure that calculates the difference between ResolvedDate and CreatedDate in hours. The DATEDIFF function returns an integer when we use HOUR, so the average will be in whole hours. To get decimals we can use DATEDIFF in minutes and divide by 60.

Code explanation: we filter Tickets to exclude rows with missing or malformed dates, and ensure ResolvedDate is greater than or equal to CreatedDate. Then we use AVERAGEX over that set.

Avg Resolution Hours =
VAR ResolvedTickets =
    FILTER(
        Tickets,
        NOT(ISBLANK(Tickets[ResolvedDate]))
            && NOT(ISBLANK(Tickets[CreatedDate]))
            && Tickets[ResolvedDate] >= Tickets[CreatedDate]
    )
RETURN
    AVERAGEX(
        ResolvedTickets,
        DATEDIFF(Tickets[CreatedDate], Tickets[ResolvedDate], HOUR)
    )

If you want decimal precision at the minute level, use:

Avg Resolution Hours Precise =
VAR Resolved =
    FILTER(
        Tickets,
        NOT(ISBLANK(Tickets[ResolvedDate]))
            && NOT(ISBLANK(Tickets[CreatedDate]))
            && Tickets[ResolvedDate] >= Tickets[CreatedDate]
    )
RETURN
    AVERAGEX(Resolved, DATEDIFF(Tickets[CreatedDate], Tickets[ResolvedDate], MINUTE) / 60)

Numerical example: in a set of 10,000 resolved tickets, if the sum of differences in hours is 52,000, the average will be 5.2 hours per ticket.

Step 3: Handle outliers and extreme values

Outliers can distort the average. For example, if 1% of tickets took 365 days to resolve, the average rises significantly. A simple approach is to exclude durations above a reasonable threshold, for example 90 days. In 10,000 tickets, 90 days = 2160 hours; if 200 tickets exceed this limit, we are excluding 2% of cases.

Avg Resolution Hours (Trimmed) =
VAR MaxHours = 90 * 24
VAR ResolvedTickets =
    FILTER(
        Tickets,
        NOT(ISBLANK(Tickets[ResolvedDate]))
            && NOT(ISBLANK(Tickets[CreatedDate]))
            && Tickets[ResolvedDate] >= Tickets[CreatedDate]
            && DATEDIFF(Tickets[CreatedDate], Tickets[ResolvedDate], HOUR) <= MaxHours
    )
RETURN
    IF(
        COUNTROWS(ResolvedTickets)=0,
        BLANK(),
        AVERAGEX(ResolvedTickets, DATEDIFF(Tickets[CreatedDate], Tickets[ResolvedDate], HOUR))
    )

Alternatives: use medians for a robust measure or calculate percentiles such as the 90th percentile to understand the distribution. Always verify how many rows were excluded with a count measure to confirm the cutoff is not removing too much legitimate data.

Step 4: Weighted average by priority (optional)

If you want to give more weight to high-priority tickets, use a weighted average. We assume a Priority column with numeric values 1 to 5. This makes sense if, for example, a Priority 5 ticket is three times more critical than Priority 1.

Weighted Avg Resolution Hours =
VAR Resolved =
    FILTER(
        Tickets,
        NOT(ISBLANK(Tickets[ResolvedDate]))
            && NOT(ISBLANK(Tickets[CreatedDate]))
            && Tickets[ResolvedDate] >= Tickets[CreatedDate]
    )
VAR SumWeighted =
    SUMX(
        Resolved,
        DATEDIFF(Tickets[CreatedDate], Tickets[ResolvedDate], HOUR) * COALESCE(Tickets[Priority],1)
    )
VAR SumWeights =
    SUMX(Resolved, COALESCE(Tickets[Priority],1))
RETURN
    DIVIDE(SumWeighted, SumWeights)

If Priority is text (High, Medium, Low), convert it first with SWITCH or create a mapping column.

Step 5: Show in days and format for the user

To show the result in days with one decimal place convert hours to days. In Power BI, set the measure format to Display as Number with 1 decimal place.

Avg Resolution Days =
VAR Hours = [Avg Resolution Hours]
RETURN
    IF(ISBLANK(Hours), BLANK(), Hours / 24)

If you prefer hh:mm show whole hours and minutes with INT and MOD or use FORMAT to format a string but avoid FORMAT in metrics that will be used in calculations.

Validate the result

Create a card or table visual with the measures: code Avg Resolution Hours, code Avg Resolution Hours (Trimmed) and code Avg Resolution Days. Filter by month or team to confirm that values change as expected. Compare with a sample table of 50 tickets: manually calculate some examples to validate the average.

Useful measures for validation:

Resolved Count =
CALCULATE(COUNTROWS(Tickets), NOT(ISBLANK(Tickets[ResolvedDate])))

Trimmed Count =
CALCULATE(
    COUNTROWS(Tickets),
    NOT(ISBLANK(Tickets[ResolvedDate])),
    DATEDIFF(Tickets[CreatedDate], Tickets[ResolvedDate], HOUR) <= 90*24
)

Check that Resolved Count and Trimmed Count make sense and that the number excluded is plausible (for example 1-5% depending on history).

Conclusion

You now know how to calculate average resolution time in DAX, handle outliers and apply weighting by priority. Recommended next steps: calculate percentiles (e.g. 90th percentile) to understand the distribution tail, create a measure for the percentage of tickets within SLA and ensure dates are in a consistent time zone before calculating durations. Performance tip: use variables, avoid unnecessary iterators on large tables and pre-aggregate when possible for models with millions of rows.