Forum Discussion
COUNTIFS (if) dates meet criteria
Hi all,
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 employees 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 |
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 :)
5 Replies
- dedelman_clng
Community Champion
I have everything for you except for the exact format of the table that you want, but if that's just for reference this ought to work for you.
In Edit Query, highlight the 3 date columns, go to the Transform tab and click "Unpivot Columns"
Rename the columns "Training Type" and "Training Date"
Back in the Report, create a calculated column DateStatus (adjust logic as needed)
DateStatus = IF ( DATEDIFF ( Training[Training Date], TODAY (), MONTH ) < 12, "In Date", "Out of Date" )Create a measure Pct Trained
Pct Trained = DIVIDE ( CALCULATE ( COUNTA ( Training[Employee] ), Training[DateStatus] = "In Date" ), CALCULATE ( COUNTA ( Training[Employee] ), ALL ( Training[DateStatus] ) ) )This will give you what you need. You can create a matrix with Training Type as the rows, Dept and Training Status as the columns, and count of Employee as the value
I couldn't get Pct Trained in the matrix as well without it repeating inside each department. But if you want Pct trained for graphs, etc, this should give you the setup you need.
Hope this helps
David
Thank you so much David. I'm almost there, but I'm having some difficulties in understanting how to build the final table, similar to your last screenshot.
I'd appreciate an advice on this :)
Thanks,
Maciek
v-chuncz-msftCommunity Support
You may drag the measures below to Values.
# of staff per dept = COUNT ( Training[Employee] )
In Date = CALCULATE ( [# of staff per dept], DATEDIFF ( Training[Training Date], TODAY (), MONTH ) <= 12 )Out of date = CALCULATE ( [# of staff per dept], DATEDIFF ( Training[Training Date], TODAY (), MONTH ) > 12 )% of staff trained = DIVIDE ( [In Date], [# of staff per dept] )
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 :)
