Forum Discussion
COUNTIFS (if) dates meet criteria
Hi all,
I'm not gonna bore you with how new I am to Power BI so I'll start now with my question ;)
I have a list in SharePoint Online called Training Matrix. It contains all employees and tens of columns with Course names and those contain a date of training.
Each course has a different frequency, but let's assume all courses here should be completed every 12 months and there are 7 staff members in each department. So, dates within last 12 months should be counted as "In date" and dates that are over 12 months old should be counted as "Out of date". I got number of staff per department sorted, I just want to show a percantage of how many staff are trained per department.
SharePoint list (Training Matrix):
| Employee | Department | Fire | H&S | GDPR |
| Amy Fittock | Dept C | 13/10/2018 | 05/09/2017 | 05/09/2017 |
| Bernarda Chauez | Dept A | 01/11/2018 | 01/01/2017 | 04/02/2018 |
| Carlyn Preciado | Dept A | 01/05/2017 | 02/07/2018 | 01/05/2017 |
| Catrice Gayle | Dept B | 01/01/2017 | 04/02/2018 | 01/11/2018 |
| Clarita Netto | Dept A | 01/02/2016 | 04/09/2018 | 24/07/2018 |
| Delorse Marietta | Dept B | 01/01/2017 | 04/02/2018 | 01/11/2018 |
| Elayne Ord | Dept B | 04/09/2018 | 24/07/2018 | 01/02/2016 |
| Fernanda Hardage | Dept A | 01/02/2016 | 04/09/2018 | 24/07/2018 |
| Jeanelle Jimerson | Dept C | 24/07/2018 | 01/02/2016 | 04/09/2018 |
| Jerri Tamura | Dept C | 01/05/2017 | 01/05/2017 | 02/07/2018 |
| Kaila Pidgeon | Dept B | 04/09/2018 | 24/07/2018 | 01/02/2016 |
| Kenton Sowards | Dept C | 24/07/2018 | 01/02/2016 | 04/09/2018 |
| Layne Waggoner | Dept A | 05/09/2017 | 05/09/2017 | 13/10/2018 |
| Madlyn Michael | Dept C | 04/02/2018 | 01/11/2018 | 01/01/2017 |
| Mellissa Watchman | Dept B | 02/07/2018 | 01/05/2017 | 01/05/2017 |
| Nicola Said | Dept C | 04/02/2018 | 01/11/2018 | 01/01/2017 |
| Ryann Misner | Dept C | 13/10/2018 | 05/09/2017 | 05/09/2017 |
| Senaida Hippert | Dept A | 05/09/2017 | 05/09/2017 | 13/10/2018 |
| Sharlene Mcclurg | Dept B | 05/09/2017 | 13/10/2018 | 05/09/2017 |
| Soila Patout | Dept B | 05/09/2017 | 13/10/2018 | 05/09/2017 |
| Tyisha Flythe | Dept A | 01/11/2018 | 01/01/2017 | 04/02/2018 |
and I want this in Power BI:
| Dept A | Dept B | Dept C | ||||||||||
| # of staff per dept | In Date | Out of date | % of staff trained | # of staff per dept | In Date | Out of date | % of staff trained | # of staff per dept | In Date | Out of date | % of staff trained | |
| Fire | 7 | 2 | 5 | 29% | 7 | 3 | 4 | 43% | 7 | 6 | 1 | 86% |
| H&S | 7 | 3 | 4 | 43% | 7 | 6 | 1 | 86% | 7 | 2 | 5 | 29% |
| GDPR | 7 | 6 | 1 | 86% | 7 | 2 | 5 | 29% | 7 | 3 | 4 | 43% |
I then want to display results graphically, per department, per course etc (I think this part should be easier).
I would apreciate any advise on how to achieve this :)