Average months between missing and hitting SLA

[Anonymous]

Hi, 

I'm trying to work out the average periods (months) between missing SLA and hitting SLA. My data is refreshed monthly. For example; if a campaign misses in January 2019 and hits in February 2019 then the average period it takes is 1 month. 

I need to take into effect that a campaign might miss their SLA in January, hit it in February - which would make it 1 month. The same campaign might miss again in May and June and hit again in July - which would make it 2 months. The average then for this campaign is 1.5 months. 

I also need to take into account that if a campaign misses in June and hasn't hit SLA again since then the average of that period would be a datediff from June to now. 

The fields in my database are;

- Date (date)

- Client (nvarchar)

- Actual (float)

- Target (float)

- FTE (int)

Many Thanks!

[v-lili6-msft]

HI, @Anonymous 

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft , 

Below is my sample data. 

I will be looking to show this in a table like this: 

Desired Output

Client	Average Months to recover
Client1	1.5
Client2	2
Client3	0
Client4	3
Client5	2.5
Client6	1
Client7	1
Client8	2
Client9	3

Sample Data

Date	Client	Actual	Target	FTE
2019/05/01	Client1	0.68	0.85	2
2019/05/01	Client2	0.81	0.55	11
2019/05/01	Client3	0.85	0.8	NULL
2019/05/01	Client4	0.92	0.85	24
2019/05/01	Client5	0.87	0.8	10
2019/05/01	Client6	0.76	0.8	14
2019/05/01	Client7	0.87	0.8	16
2019/05/01	Client8	0.96	0.8	7
2019/05/01	Client9	1	0.95	1
2019/05/01	Client10	0.96	0.8	5
2019/05/01	Client11	0.92	0.8	12
2019/05/01	Client12	0.92	0.8	16
2019/05/01	Client13	0.93	0.85	14
2019/05/01	Client14	0.84	0.9	95
2019/05/01	Client15	0.97	0.98	47
2019/05/01	Client16	0.9	0.75	28
2019/06/01	Client1	0.79	0.85	2
2019/06/01	Client2	0.59	0.55	11
2019/06/01	Client3	0.79	0.8	NULL
2019/06/01	Client4	0.78	0.8	NULL
2019/06/01	Client5	0.7	0.85	24
2019/06/01	Client6	0.9	0.8	10
2019/06/01	Client7	0.81	0.8	14
2019/06/01	Client8	0.82	0.8	16
2019/06/01	Client9	0.98	0.8	7
2019/06/01	Client10	1	0.95	1
2019/06/01	Client11	0.98	0.8	5
2019/06/01	Client12	0.8	0.8	12
2019/06/01	Client13	0.92	0.8	16
2019/06/01	Client14	0.89	0.8	16
2019/06/01	Client15	0.94	0.8	16
2019/06/01	Client16	0.89	0.85	95
2019/06/01	Client17	0.9	0.9	14
2019/06/01	Client18	0.96	0.95	47
2019/06/01	Client19	0.96	0.95	47
2019/06/01	Client20	0.87	0.75	28
2019/07/01	Client1	0.975	0.96	47
2019/07/01	Client2	0.28	0.85	24
2019/07/01	Client3	0.84	0.75	28
2019/07/01	Client4	0.59	0.55	11
2019/07/01	Client5	0.97	0.8	7
2019/07/01	Client6	0.98	0.8	5
2019/07/01	Client7	0.81	0.8	16
2019/07/01	Client8	1	0.95	1
2019/07/01	Client9	0.71	0.8	16
2019/07/01	Client10	0.76	0.8	14
2019/07/01	Client11	0.92	0.85	14
2019/07/01	Client12	0.9	0.8	10
2019/07/01	Client13	0.82	0.8	NULL
2019/07/01	Client14	0.92	0.9	95