Can You Automate SLA Reporting Without Buying New Software?

Yes. If your ticketing or telephony system can export a file, Excel with Power Query, or Power BI if you already have it, can turn a manual weekly SLA pull into a report that refreshes itself. You do not need a new platform for that.

The automation is the easy part. What takes the time is agreeing what the SLA number actually is, and the same five problems come up every time an SLA report gets rebuilt. Each section below has the fix in Power Query or DAX, in the order the data flows through the report.

The examples assume a ticket export with one row per ticket: TicketID, CreatedTime, ResolvedTime (blank while the ticket is open), Priority, ReopenCount (how many times the ticket was reopened), and a small lookup that gives the SLA target in minutes for each priority. Support hours are Monday to Friday, 08:00 to 18:00, in Central European time.

1. Timestamps in the wrong time zone

Most systems export in UTC, while the SLA runs on local support hours. A ticket created at 08:30 in Hamburg is 06:30 UTC in summer and 07:30 UTC in winter. Apply business-hours logic to UTC times and every result is wrong by an hour for half the year.

Convert once, at the start of the query, and never again. Power Query has no named time zones, so for Central Europe the daylight saving rule goes into a function. Summer time runs from 01:00 UTC on the last Sunday of March to 01:00 UTC on the last Sunday of October.

// Query name: UtcToCentralEurope
(utc as datetime) as datetime =>
let
    year = Date.Year(utc),
    lastSundayOf = (month as number) =>
        let
            lastDay = #date(year, month, 31)
        in
            Date.AddDays(lastDay, -Date.DayOfWeek(lastDay, Day.Sunday)),
    summerStart = DateTime.From(lastSundayOf(3)) + #duration(0, 1, 0, 0),
    summerEnd = DateTime.From(lastSundayOf(10)) + #duration(0, 1, 0, 0),
    offsetHours = if utc >= summerStart and utc < summerEnd then 2 else 1
in
    utc + #duration(0, offsetHours, 0, 0)

I checked this rule against the Europe/Berlin time zone rules for every hour from 2024 to 2030, and it matched each time. Apply it to both ticket columns:

let
    Source = Tickets,
    Local = Table.TransformColumns(
        Source,
        {
            {"CreatedTime", UtcToCentralEurope, type datetime},
            {"ResolvedTime", each if _ = null then null else UtcToCentralEurope(_), type nullable datetime}
        }
    )
in
    Local

Open tickets need a “now” to measure against. Power BI’s service runs in UTC while Excel on your desktop runs in local time, so DateTime.LocalNow() gives different answers depending on where the refresh happens. Define the refresh time once, from UTC:

// Query name: RefreshTimeLocal
UtcToCentralEurope(DateTimeZone.RemoveZone(DateTimeZone.UtcNow()))

2. The clock should only run during support hours

A ticket opened on Friday at 17:00 with a 4-hour target is not late on Saturday morning. Calendar time overstates breaches, and the gap grows with every weekend and public holiday.

This function counts only the minutes that fall inside support hours, skipping weekends and a list of holidays:

// Query name: BusinessMinutes
(startTime as datetime, endTime as datetime, holidays as list) as number =>
if endTime <= startTime then 0 else
let
    openHour = 8,
    closeHour = 18,
    firstDay = Date.From(startTime),
    dayCount = Duration.Days(Date.From(endTime) - firstDay) + 1,
    allDays = List.Dates(firstDay, dayCount, #duration(1, 0, 0, 0)),
    workDays = List.Select(
        allDays,
        each Date.DayOfWeek(_, Day.Monday) < 5 and not List.Contains(holidays, _)
    ),
    minutesPerDay = List.Transform(
        workDays,
        (day) =>
            let
                opens = DateTime.From(day) + #duration(0, openHour, 0, 0),
                closes = DateTime.From(day) + #duration(0, closeHour, 0, 0),
                windowStart = List.Max({startTime, opens}),
                windowEnd = List.Min({endTime, closes}),
                minutes = Duration.TotalMinutes(windowEnd - windowStart)
            in
                if minutes > 0 then minutes else 0
    )
in
    if List.Count(minutesPerDay) = 0 then 0 else List.Sum(minutesPerDay)

The first line returns 0 when the end time is not after the start time, which can happen with clock skew or a bad ResolvedTime. Without it, a negative date range would raise an error and stop the refresh.

Holidays is a query that returns a list of dates, for example List.Buffer(Table.Column(HolidayTable, "Date")). Buffering reads the list once, instead of on every row. I tested the same logic on cases such as Friday 17:00 to Monday 09:00 (120 minutes) and a range that spans a holiday, and it returned the expected minutes each time. Use it like this:

Table.AddColumn(
    Local,
    "ActiveBizMinutes",
    each BusinessMinutes(
        [CreatedTime],
        if [ResolvedTime] = null then RefreshTimeLocal else [ResolvedTime],
        Holidays
    ),
    type number
)

The function loops over every day in the range, which is fine for thousands of tickets. With millions of rows, do this step in the source system or in a database instead.

3. The clock should stop while you wait for the customer

Most SLAs pause while a ticket is in a “waiting for customer” status. If your export only shows the current status, you cannot rebuild those pauses, and the report will overstate breaches. Ask your system administrator for a status history export, with one row per ticket per status period, or agree that the report shows total elapsed support time and write that in the definition.

With a status history, first convert its two time columns exactly as in section 1, because this is a different table with its own timestamps. Then count business minutes only for the statuses where the clock runs:

let
    Source = StatusHistory,
    Local = Table.TransformColumns(
        Source,
        {
            {"StartTime", UtcToCentralEurope, type datetime},
            {"EndTime", each if _ = null then null else UtcToCentralEurope(_), type nullable datetime}
        }
    ),
    Running = Table.SelectRows(Local, each List.Contains({"New", "Open", "In progress"}, [Status])),
    WithMinutes = Table.AddColumn(
        Running,
        "BizMinutes",
        each BusinessMinutes(
            [StartTime],
            if [EndTime] = null then RefreshTimeLocal else [EndTime],
            Holidays
        ),
        type number
    ),
    PerTicket = Table.Group(
        WithMinutes,
        {"TicketID"},
        {{"ActiveBizMinutes", each List.Sum([BizMinutes]), type number}}
    )
in
    PerTicket

The status names in the list have to match your system exactly. Take them from the export, not from memory.

4. Reopened tickets

When a ticket is resolved and then reopened, does the SLA clock restart? Two answers are common, and both are legitimate. Each one gives a different report.

If the clock restarts, the ticket can be counted twice, or a first breach can disappear behind a second resolution. If you measure against the first resolution, the SLA shows how fast the team responded and reopens show up as a separate quality problem. I prefer the second, and I report the reopen rate next to the SLA number:

Reopen Rate % =
DIVIDE (
    CALCULATE ( COUNTROWS ( Tickets ), Tickets[ReopenCount] > 0 ),
    COUNTROWS ( Tickets )
)

In Power Query, keep the first resolution time for each ticket and use it in the steps above, not the latest one. Whichever rule you choose, write it in the report.

5. The denominator

Which tickets count? Cancelled tickets, merged duplicates and spam should not be in the SLA. And a ticket that is still open but already past its target should count as a breach today, not on the day someone finally closes it.

Merge the priority lookup into the ticket table to get TargetMinutes, remove out-of-scope tickets, then give every ticket one of three statuses:

Table.AddColumn(
    WithTargets,
    "SlaStatus",
    each if [ActiveBizMinutes] > [TargetMinutes] then "Breached"
        else if [ResolvedTime] <> null then "Met"
        else "Pending",
    type text
)

Then the measures stay simple:

Tickets Met = CALCULATE ( COUNTROWS ( Tickets ), Tickets[SlaStatus] = "Met" )

Tickets Breached = CALCULATE ( COUNTROWS ( Tickets ), Tickets[SlaStatus] = "Breached" )

SLA % = DIVIDE ( [Tickets Met], [Tickets Met] + [Tickets Breached] )

Pending tickets are left out of both sides. They have not failed yet and they have not succeeded yet.

Making it refresh by itself

A query does not refresh itself in Excel. It refreshes when something tells it to.

For a weekly review, the simplest setting is in Queries and Connections: open the query properties and turn on “Refresh data when opening the file”. The report then updates whenever someone opens it.

A closed Excel file will not refresh on its own. If the report must be current before anyone opens it, the usual answer is the Power BI service with a scheduled refresh. That needs a Pro or Premium Per User license, plus a data gateway if the source is a local file or an on-premises system. If you already have those, it is not new software.

When you do need something else

Three cases justify a new tool. You need real-time alerts, so a breach is flagged the moment it happens and not the next time someone opens a report. Your source system has no export and no API. Or your volumes are past what Excel handles comfortably, which is somewhere in the millions of rows. Outside those three, a manual SLA report usually means the definition was never written down, and a new platform would only automate the confusion.

The same steps, from raw export to a finished dashboard, are in From Raw Ticket Data to Executive Dashboard.