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

Average months between missing and hitting SLA

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!

2 REPLIES 2
Community Support
Community Support

Re: Average months between missing and hitting SLA

HI, @thomas1395 

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thomas1395
Frequent Visitor

Re: Average months between missing and hitting SLA

Hi @v-lili6-msft

 

Below is my sample data. 

 

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

Desired Output

ClientAverage Months to recover
Client11.5
Client22
Client30
Client43
Client52.5
Client61
Client71
Client82
Client93

 

 

Sample Data

DateClientActualTargetFTE
2019/05/01Client10.680.852
2019/05/01Client20.810.5511
2019/05/01Client30.850.8NULL
2019/05/01Client40.920.8524
2019/05/01Client50.870.810
2019/05/01Client60.760.814
2019/05/01Client70.870.816
2019/05/01Client80.960.87
2019/05/01Client910.951
2019/05/01Client100.960.85
2019/05/01Client110.920.812
2019/05/01Client120.920.816
2019/05/01Client130.930.8514
2019/05/01Client140.840.995
2019/05/01Client150.970.9847
2019/05/01Client160.90.7528
2019/06/01Client10.790.852
2019/06/01Client20.590.5511
2019/06/01Client30.790.8NULL
2019/06/01Client40.780.8NULL
2019/06/01Client50.70.8524
2019/06/01Client60.90.810
2019/06/01Client70.810.814
2019/06/01Client80.820.816
2019/06/01Client90.980.87
2019/06/01Client1010.951
2019/06/01Client110.980.85
2019/06/01Client120.80.812
2019/06/01Client130.920.816
2019/06/01Client140.890.816
2019/06/01Client150.940.816
2019/06/01Client160.890.8595
2019/06/01Client170.90.914
2019/06/01Client180.960.9547
2019/06/01Client190.960.9547
2019/06/01Client200.870.7528
2019/07/01Client10.9750.9647
2019/07/01Client20.280.8524
2019/07/01Client30.840.7528
2019/07/01Client40.590.5511
2019/07/01Client50.970.87
2019/07/01Client60.980.85
2019/07/01Client70.810.816
2019/07/01Client810.951
2019/07/01Client90.710.816
2019/07/01Client100.760.814
2019/07/01Client110.920.8514
2019/07/01Client120.90.810
2019/07/01Client130.820.8NULL
2019/07/01Client140.920.995

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors