Hello
Please help confirm if my calculations are correct and also help with tweaking them. I am calculating Service level agreements for different departments(queues).
1. If a department has resolved issues within the agreed number of hours then add 1 if not 0, then get the sum to calculate SLA.
2. These tickets have priorities as well and the priorities are tied into the number of hours(e.g urgent could be resolved in 3 hours)
3. Would also like for the tickets with status "On hold" and "Information" requested to show a pause when calculating SLA( Current idea is Time to solve-Days on Status)
Sample data
| Ticket Queue | First Assigned On | Status | Priority | Solved On |
| SPARK Randpark Ridge | 2021-02-08 09:27:33 +0200 | closed | normal | 2021-02-08 21:36:03 +0200 |
| SPARK Randpark Ridge | 2021-02-08 08:13:17 +0200 | closed | normal | 2021-02-08 21:34:53 +0200 |
| SPARK Randpark Ridge | 2021-02-07 12:20:42 +0200 | closed | normal | 2021-02-08 21:31:44 +0200 |
| SPARK Randpark Ridge | 2021-02-08 07:04:45 +0200 | closed | normal | 2021-02-08 21:33:00 +0200 |
| SPARK Bramley | 2021-02-08 21:32:01 +0200 | new | normal | |
| SPARK Cresta | 2021-01-28 08:08:15 +0200 | information requested | normal | |
| SPARK Cresta | 2021-01-27 10:22:18 +0200 | closed | normal | 2021-02-08 21:29:39 +0200 |
| SPARK Cresta | 2021-02-04 09:25:36 +0200 | information requested | normal | |
| SPARK Randpark Ridge | 2021-02-05 16:03:17 +0200 | closed | normal | 2021-02-08 21:27:48 +0200 |
| SPARK Randpark Ridge | 2021-02-05 13:15:18 +0200 | closed | normal | 2021-02-08 21:26:21 +0200 |
| SPARK Cresta | 2021-02-03 20:23:02 +0200 | closed | normal | 2021-02-08 21:26:48 +0200 |
| SPARK Randpark Ridge | 2021-02-04 06:19:20 +0200 | closed | normal | 2021-02-08 19:15:26 +0200 |
| SPARK Cresta | 2021-02-03 12:59:36 +0200 | closed | normal | 2021-02-08 21:25:24 +0200 |
| SPARK Randpark Ridge | 2021-02-05 12:48:05 +0200 | closed | normal | 2021-02-08 21:25:09 +0200 |
| SPARK Cresta | 2021-02-03 08:41:19 +0200 | closed | normal | 2021-02-08 21:24:25 +0200 |
| SPARK Randpark Ridge | 2021-02-05 12:35:32 +0200 | closed | normal | 2021-02-08 21:23:44 +0200 |
| SPARK Cresta | 2021-02-03 01:56:07 +0200 | closed | normal | 2021-02-08 21:23:34 +0200 |
| SPARK Rosslyn Hub | 2021-02-08 21:23:14 +0200 | new | normal | |
| SPARK Cresta | 2021-02-02 13:30:55 +0200 | closed | normal | 2021-02-08 21:22:35 +0200 |
| SPARK Cresta | 2021-02-01 12:56:07 +0200 | solved | normal | 2021-02-08 21:21:04 +0200 |
| SPARK Randpark Ridge | 2021-02-05 09:50:39 +0200 | closed | normal | 2021-02-08 21:20:36 +0200 |
| Accounts Mailbox | 2021-02-08 21:20:33 +0200 | on hold | normal | |
| SPARK Rosslyn Hub | 2021-02-08 21:19:44 +0200 | on hold | normal | |
| SPARK Randpark Ridge | 2021-02-04 21:05:17 +0200 | solved | normal | 2021-02-08 21:16:51 +0200 |
| SPARK Randpark Ridge | 2021-02-04 15:36:37 +0200 | closed | normal | 2021-02-08 21:15:20 +0200 |
| SPARK Randpark Ridge | 2021-02-04 10:25:34 +0200 | closed | normal | 2021-02-08 21:12:44 +0200 |
| SPARK Cresta | 2021-02-01 10:38:30 +0200 | solved | normal | 2021-02-08 21:09:07 +0200 |
| SPARK Randpark Ridge | 2021-02-04 09:39:55 +0200 | closed | normal | 2021-02-08 21:06:56 +0200 |
| SPARK Randpark Ridge | 2021-02-04 08:28:57 +0200 | closed | normal | 2021-02-08 21:04:29 +0200 |
| Accounts Mailbox | 2021-02-08 21:04:12 +0200 | in progress | normal | |
| SPARK Randpark Ridge | 2021-02-04 07:18:48 +0200 | closed | normal | 2021-02-08 21:01:41 +0200 |
| SPARK Kempton Park | 2021-02-08 20:56:23 +0200 | new | normal | |
| SPARK Kempton Park | 2021-02-03 08:15:20 +0200 | closed | normal | 2021-02-08 13:23:30 +0200 |
| SPARK Randpark Ridge | 2021-02-05 11:08:28 +0200 | closed | normal | 2021-02-08 20:52:10 +0200 |
Sample Power BI
Hours = IF(ISBLANK('Report Data'[Solved On].[Date]), BLANK(), DATEDIFF('Report Data'[First Assigned On].[Date], 'Report Data'[Solved On].[Date], HOUR))
Overall SLA Compliance = SWITCH(True(), AND('Report Data'[Status] <> "new", AND(OR(AND(OR('Report Data'[Priority] = "normal", 'Report Data'[Priority] = "low"), 'Report Data'[Hours]<=120), AND(OR('Report Data'[Priority] = "urgent", 'Report Data'[Priority] = "emergency"), 'Report Data'[Hours]<=48)),Or(Or('Report Data'[Ticket Queue] = "Marketing", 'Report Data'[Ticket Queue] = "Health and Safety"), 'Report Data'[Ticket Queue] = "Human Capital"))), "1", AND('Report Data'[Status] <> "new", AND(OR(AND(OR('Report Data'[Priority] = "normal", 'Report Data'[Priority] = "low"), 'Report Data'[Hours]<=720), AND(OR('Report Data'[Priority] = "urgent", 'Report Data'[Priority] = "emergency"), 'Report Data'[Hours]<=72)),Or(Or('Report Data'[Ticket Queue] = "Procurement", 'Report Data'[Ticket Queue] = "Facilities"), 'Report Data'[Ticket Queue] = "IT Operations"))), "1", AND('Report Data'[Status] <> "new", AND(OR(AND(OR('Report Data'[Priority] = "normal", 'Report Data'[Priority] = "low"), 'Report Data'[Hours]<=96), AND(OR('Report Data'[Priority] = "urgent", 'Report Data'[Priority] = "emergency"), 'Report Data'[Hours]<=48)),Or(Or('Report Data'[Ticket Queue] = "SPARK Schools At Home", 'Report Data'[Ticket Queue] = "Accounts Receivable"), 'Report Data'[Ticket Queue] = "Credit Control"))), "1", AND('Report Data'[Status] <> "new", AND(OR(AND(OR('Report Data'[Priority] ="normal", 'Report Data'[Priority] = "low"), 'Report Data'[Hours]<=120), AND(OR('Report Data'[Priority] = "urgent", 'Report Data'[Priority] = "emergency"), 'Report Data'[Hours]<=48)),Or(Or('Report Data'[Ticket Queue] = "Learning Model", 'Report Data'[Ticket Queue] = "Business Intelligence"), 'Report Data'[Ticket Queue] = "Systems Team"))), "1", AND(AND('Report Data'[Status] <> "new", 'Report Data'[Status] <> "closed")
