SLA Achivement Calculation

[SergioHB]

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.

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.

The same calculation for Responsiveness (First response)

Any help on this would be very welcome.

Thanks

[Icey · Accepted solution]

Hi @SergioHB ,

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

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]

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]

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]

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]

Hi @Icey 

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

Thanks

[Icey]

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.