cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SergioHB Frequent Visitor
Frequent Visitor

SLA Achivement Calculation

Hi guys,

 

I'm starting with PowerBI and DAX and would like if some could give me a hand on the following. I'm creating a dashboard to follow-up SLA achivement depending on priorities but I don't know how can I determinate dinamically when a case has hit the first reponse or the resolution SLAs.

 

Following a table with some data to explain. The achivement columns should have a null or 1 depending if the case has achieved the SLA timings according to the nexts tables.

 

TableCases_Example.png

This small table that should be counting cases by priority and also counting how many of them have achieved the SLA with a % of the total.

 

TableResolutiontime_SLA.png

The same calculation for Responsiveness (First response)

 

TableResponsiveness_SLA.png

 

Any help on this would be very welcome.

Thanks

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Icey Super Contributor
Super Contributor

Re: SLA Achivement Calculation

Hi @SergioHB ,

Is this what you want? This is my PBIX file.

closed.PNG

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Icey Super Contributor
Super Contributor

Re: SLA Achivement Calculation

Hi @SergioHB ,

Is this what you want? This is my PBIX file.

closed.PNG

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

SergioHB Frequent Visitor
Frequent Visitor

Re: SLA Achivement Calculation

Hi @Icey 

Thanks so much for helping on this. Apparently it's working as I expected but just a couple of measures are not calculating on my side. They are:

  • Achieved Resolution
  • Achieved Response

with the following message: "A single value for column 'ClosedDate' in table 'Case' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.."

 

Any idea?

Icey Super Contributor
Super Contributor

Re: SLA Achivement Calculation

Hi @SergioHB ,

You can create the two measures like so:

Achieved Response Measure =
IF ( ISBLANK ( MAX ( 'Table'[Initial_Response] ) ), BLANK (), 1 )
Achieved Resolution Measure =
IF ( ISBLANK ( MAX ( 'Table'[ClosedDate] ) ), BLANK (), 1 )

In the pbix file I shared before, these two are columns.

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

SergioHB Frequent Visitor
Frequent Visitor

Re: SLA Achivement Calculation

Thank @Icey ,

 

I'll check this along the day but looks fine to me. Only one last question. 

 

The column 'Closed' is just counting cases from the period, but what if the cases were created in the past and closed now?

 

Example:

 

In September there are 150 cases logged and from those 140 closed, so 10 pending. That does not mean that in September only 140 cases are closed, there can be closed cases from past months (previous backlog). There can be more closed than logged cases.

 

Do you get my point? I don't think this has been taken in consideration when you define the 'Closed' column.

 

Thanks

 

 

 

SergioHB Frequent Visitor
Frequent Visitor

Re: SLA Achivement Calculation

Hi @Icey 

 

No reply to my last email. Did you have a chance to have a look at it?

 

Thanks

Highlighted
Icey Super Contributor
Super Contributor

Re: SLA Achivement Calculation

Hi @SergioHB ,

Sorry to reply late. You can create a column:

Month of CreatedDate = MONTH ( 'Table'[CreatedDate] )

Then, you can create your "Closed" measure like so:

Closed =
CALCULATE (
    COUNT ( 'Table'[ClosedDate] ),
    ALLSELECTED ( 'Table'[Month of CreatedDate] )
)

If your created years are also different, you can create a column of Created Year too. And then, add it into "Closed" measure.

 

Best Regards,
Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)