Number of times monthly SLA missed during last six month

[sudhir_gawade]

Hi Guys,

I have a senario. I have to calculate monthly SLA and compare against montly target and then I have to count during last six month how many time (month) we have missed the SLA.

Below is my data. monthly target is 95%.

Thanks in Advance.

Date	Total	Not met
1-Apr	122	4
2-Apr	133	6
3-Apr	204	8
4-Apr	154	10
5-Apr	115	4
8-Apr	95	9
9-Apr	130	1
10-Apr	193	19
11-Apr	161	12
12-Apr	160	3
15-Apr	139	4
16-Apr	166	11
17-Apr	170	13
18-Apr	172	10
24-Apr	319	15
25-Apr	169	1
26-Apr	173	10
29-Apr	151	4
30-Apr	252	7
1-May	248	24
2-May	169	5
3-May	124	3
7-May	140	1
8-May	153	9
9-May	166	6
10-May	129	11
13-May	113	5
14-May	172	10
15-May	190	17
16-May	169	10
17-May	130	3
20-May	132	13
21-May	172	1
22-May	219	2
23-May	167	5
24-May	157	7
28-May	154	9
29-May	246	12
30-May	164	13
31-May	174	12
3-Jun	117	5
4-Jun	119	2
5-Jun	178	10
6-Jun	146	4
7-Jun	86	6
10-Jun	102	5
11-Jun	132	2
12-Jun	190	13
13-Jun	177	5
14-Jun	121	9
17-Jun	111	9
18-Jun	136	4
19-Jun	182	9
20-Jun	148	14
21-Jun	111	4
24-Jun	113	5
25-Jun	118	3
26-Jun	238	16
27-Jun	162	1
28-Jun	164	16

[v-lid-msft · Accepted solution]

Hi @sudhir_gawade ,

We can create a measure to calculate the SLA,

SLA =
( SUM ( SLA[Total] ) - SUM ( SLA[Not Meet] ) ) / SUM ( SLA[Total] )

Then we can create a table to show the SLA of every month

Table = 
VAR t =
    SUMMARIZE ( 'SLA', [Date].[Month] )
RETURN
    ADDCOLUMNS (
        t,
        "SLA", CALCULATE ( [SLA], FILTER ( ALL ( SLA ), [Month] = 'SLA'[Date].[Month] ) )
    )

Then we can use a measure to count the missing month of the previous 6 month.

MissCount = 
COUNTROWS (
    FILTER (
        FILTER (
            'Table',
            SWITCH (
                [Month],
                "January", 1,
                "February", 2,
                "March", 3,
                "April", 4,
                "May", 5,
                "June", 6,
                "July", 7,
                "August", 8,
                "September", 9,
                "October", 10,
                "November", 11,
                "December", 12
            )
                >= MONTH ( TODAY () - 5 )
        ),
        [SLA] < 0.95
    )
)

BTW, pbix as attached.

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more 

[sudhir_gawade]

Thank you