How to Calculate SLA Based on Status Transitions and Updated By in Power BI

[mwangim]

Hi all,

I'm trying to calculate SLA durations in Power BI using a table that tracks the status history of a single claim.

Here’s how I want to calculate SLA durations:

1. Initial SLA: From Submitted to the first Changes Required — both updated by John Doe (the reviewer).

2. Rework SLA: For every cycle of Re-submitted (by someone else) to Changes Required (by John Doe) — each pair should be a separate SLA.

3. Final SLA: From the last Re-submitted (by another user) to Approved (by John Doe).

Important notes:

• I only want to calculate durations where John Doe is the reviewer (updated_by = John Doe).

• Each SLA is essentially a pair of statuses that marks when the reviewer starts and ends a review cycle.

• I want to ignore weekends in the SLA calculation (optional for now, but bonus if possible).

How can I:

• Create logic in Power BI to group the correct status pairs?

• Calculate the SLA duration between those paired events for the reviewer?

A screenshot of the data is attached for reference.

Thanks in advance!

[v-sshirivolu]

Hi @mwangim ,
Thanks for reaching out to the Microsoft Fabric community forum.

Step 1: Load Data

Step 2: Create Ordered Index in Power Query

• Select Source table.
• Add index column: Add Column → Index Column → From 1
• Rename it to Step Index.

Step 3: Duplicate Table and Prepare for Self Join

• Right-click TicketLog→ Duplicate → rename to Previous Log.
• In Previous Log, rename  Step Index to Previous Step Index.
• Back in Ticket Log add custom column:
  = [StepIndex] - 1
• Rename to Previous Step Index.

Step 4: Merge Queries

• In Ticket Log, go to Home → Merge Queries → Merge Queries as New.
• Merge on:
  • Ticket Log[TicketID]= PreviousLog[TicketID]
  • Ticket Log[Previous Step Index]= Previous Log[Previous Step Index]
• Join type: Left Outer.
• Expand columns from Previous Log:
  • Status → rename Previous Status
  • Updated On → rename Previous Updated On.
  • Updated By → rename Previous Updated By

Step 5: Calculate Minutes Between Transitions

• Add custom column:
  Duration.Minutes([Updated On] - [Previous Updated On])
  
  
• Rename column to Minutes In Status.
• Handle nulls for first rows (optional): replace null with 0.

Step 6: Create DAX Measures:

• SLA Duration per User (example for John Doe):
  SLA_Duration_John =
  CALCULATE(
  SUM('TicketLog'[Minutes in Status]),
  'TicketLog'[Updated By] = "John Doe"
  )
  
  
• SLA Breach Flag (e.g., over 60 minutes):
  SLA_Breach = IF('TicketLog'[Minutes in Status] > 60, 1, 0)
  
  
• SLA Compliance Percentage:
  SLA_Compliance_Percent =
  1 - DIVIDE(
  SUM('TicketLog'[SLA_Breach]),
  COUNTROWS('TicketLog')
  )
  
  

If this solution resolves your query, please mark it as accepted and give a 'Kudos' to help others find it easily.

Best Regards,
Sreeteja
Community Support Team

[v-sshirivolu]

Hi @mwangim ,

Just wanted to check if you had the opportunity to review the suggestion provided?

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-sshirivolu]

Hi @mwangim  ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution and give a 'Kudos' so that other community members can find it easily.

[danextian]

Hi @mwangim 

Can you please post a workable sample data (not an image) along with your expected result - expected SLA?

[mwangim]

Hi @danextian,

Find attached the information as requested

dim_claim_id	status	updated_on	is_active	updated_by	SLA
9832041765E	Approved	4/10/2025 15:24	True	John Doe	0
9832041765E	Re-submitted	4/10/2025 13:29	False	Jane
9832041765E	Changes Required	4/9/2025 22:39	False	John Doe
9832041765E	Under Review	4/9/2025 21:15	False	John Doe	0
9832041765E	Re-submitted	4/9/2025 20:13	False	Jane
9832041765E	Changes Required	4/2/2025 21:56	False	John Doe	2
9832041765E	Under Review	3/31/2025 15:03	False	John Doe
9832041765E	Re-submitted	3/31/2025 14:26	False	Jane
9832041765E	Changes Required	3/28/2025 22:54	False	John Doe	0
9832041765E	Re-submitted	3/28/2025 19:39	False	Jane
9832041765E	Changes Required	3/28/2025 18:51	False	John Doe	1
9832041765E	Under Review	3/28/2025 18:18	False	John Doe
9832041765E	Submitted	3/27/2025 15:02	False	Jane

[danextian · Accepted solution]

Hi @mwangim 

I'm confused why Under Review has SLA. As per your requirement, this should be on a row that has either Changes Required or Approved. Anywa, please try this:

test = 
VAR _claimID = 'Table'[dim_claim_id]
VAR _stat = 'Table'[status]
VAR _by = 'Table'[updated_by]
VAR _updatedOn = 'Table'[updated_on]
VAR _tbl =
    FILTER ( 'Table', 'Table'[dim_claim_id] = _claimID )
VAR _firstChangedRequired =
    MINX ( FILTER ( _tbl, [status] = "Changes Required" ), [updated_on] )
VAR _Submitted =
    MINX ( FILTER ( _tbl, [status] = "Submitted" ), [updated_on] )
VAR _LastResubmitted =
    MAXX ( FILTER ( _tbl, [status] = "Re-Submitted" ), [updated_on] )
VAR _initialSLA =
    IF (
        'Table'[status] = "Changes Required"
            && 'Table'[updated_on] = _firstChangedRequired,
        DATEDIFF ( _Submitted, _firstChangedRequired, DAY )
    )
VAR _ReworkSLA =
    VAR _ResubmittedBeforeCurrent =
        MAXX (
            FILTER ( _tbl, [status] = "Re-Submitted" && [updated_on] < _updatedOn ),
            [updated_on]
        )
    RETURN
        IF (
            'Table'[status] = "Changes Required"
                && _updatedOn < _LastResubmitted,
            DATEDIFF ( _ResubmittedBeforeCurrent, _updatedOn, DAY )
        )
VAR _FinalSLA =
    IF (
        'Table'[status] = "Approved",
        DATEDIFF ( _LastResubmitted, _updatedOn, DAY )
    )
RETURN
    COALESCE ( _initialSLA, _ReworkSLA, _FinalSLA )

[mwangim]

Hi @danextian,

Find attached the information as requested

dim_claim_id	status	updated_on	is_active	updated_by	SLA
9832041765E	Approved	4/10/2025 15:24	True	John Doe	0
9832041765E	Re-submitted	4/10/2025 13:29	False	Jane
9832041765E	Changes Required	4/9/2025 22:39	False	John Doe
9832041765E	Under Review	4/9/2025 21:15	False	John Doe	0
9832041765E	Re-submitted	4/9/2025 20:13	False	Jane
9832041765E	Changes Required	4/2/2025 21:56	False	John Doe	2
9832041765E	Under Review	3/31/2025 15:03	False	John Doe
9832041765E	Re-submitted	3/31/2025 14:26	False	Jane
9832041765E	Changes Required	3/28/2025 22:54	False	John Doe	0
9832041765E	Re-submitted	3/28/2025 19:39	False	Jane
9832041765E	Changes Required	3/28/2025 18:51	False	John Doe	1
9832041765E	Under Review	3/28/2025 18:18	False	John Doe
9832041765E	Submitted	3/27/2025 15:02	False	Jane

[mwangim]

Hi @danextian 
Below is a table with the information required

dim_claim_id	status	updated_on	is_active	updated_by	SLA
9832041765E	Approved	4/10/2025 15:24	True	John Doe	0
9832041765E	Re-submitted	4/10/2025 13:29	False	Jane
9832041765E	Changes Required	4/9/2025 22:39	False	John Doe
9832041765E	Under Review	4/9/2025 21:15	False	John Doe	0
9832041765E	Re-submitted	4/9/2025 20:13	False	Jane
9832041765E	Changes Required	4/2/2025 21:56	False	John Doe	2
9832041765E	Under Review	3/31/2025 15:03	False	John Doe
9832041765E	Re-submitted	3/31/2025 14:26	False	Jane
9832041765E	Changes Required	3/28/2025 22:54	False	John Doe	0
9832041765E	Re-submitted	3/28/2025 19:39	False	Jane
9832041765E	Changes Required	3/28/2025 18:51	False	John Doe	1
9832041765E	Under Review	3/28/2025 18:18	False	John Doe
9832041765E	Submitted	3/27/2025 15:02	False	Jane

[mwangim]

Hi @danextian 
Below is a table with the information required

dim_claim_id	status	updated_on	is_active	updated_by	SLA
9832041765E	Approved	4/10/2025 15:24	True	John Doe	0
9832041765E	Re-submitted	4/10/2025 13:29	False	Jane
9832041765E	Changes Required	4/9/2025 22:39	False	John Doe
9832041765E	Under Review	4/9/2025 21:15	False	John Doe	0
9832041765E	Re-submitted	4/9/2025 20:13	False	Jane
9832041765E	Changes Required	4/2/2025 21:56	False	John Doe	2
9832041765E	Under Review	3/31/2025 15:03	False	John Doe
9832041765E	Re-submitted	3/31/2025 14:26	False	Jane
9832041765E	Changes Required	3/28/2025 22:54	False	John Doe	0
9832041765E	Re-submitted	3/28/2025 19:39	False	Jane
9832041765E	Changes Required	3/28/2025 18:51	False	John Doe	1
9832041765E	Under Review	3/28/2025 18:18	False	John Doe
9832041765E	Submitted	3/27/2025 15:02	False	Jane

[burakkaragoz]

Hi @mwangim ,

You can approach this SLA calculation using Power Query. Here's a general outline:

Steps:

1. Sort the table
   Sort by claim_id and updated_on ascending.

2. Add an index per claim
   Group by claim_id, then add an index column to track the order of status changes.

3. Add next row info
   Add a custom column to get the next row’s status, date, and updated_by.

4. Filter valid SLA pairs

   • Initial SLA: Submitted → Changes Required, both by John Doe
   • Rework SLA: Re-submitted → Changes Required, where Re-submitted is by someone else, and Changes Required is by John Doe
   • Final SLA: Last Re-submitted → Approved, Approved by John Doe

5. Calculate duration
   Subtract the updated_on of the start status from the end status.

---

Optional: Exclude weekends

You can use a custom function in Power Query to count only working days between two dates.

Let me know if you want help building the logic step by step.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[v-sshirivolu]

Hi @mwangim  ,

Just wanted to check if you had the opportunity to review the suggestion provided?

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank You.