Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |