COUNTIFS (if) dates meet criteria

[GrischkePro]

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 🙂

[GrischkePro]

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 🙂

[dedelman_clng]

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

[GrischkePro]

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-msft]

@GrischkePro,

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] )

[GrischkePro]

Thanks, I'm getting there.

What I didn't mention was that some date fields are empty and those are considered as training not completed.

So, in the table below some training has not yet been completed.

Employee	Department	Fire	H&S	GDPR
Amy Fittock	Dept C	13/10/2018	05/09/2017	05/09/2017
Bernarda Chauez	Dept A		01/01/2017	04/02/2018
Carlyn Preciado	Dept A	01/05/2017	02/07/2018
Catrice Gayle	Dept B	01/01/2017		01/11/2018
Clarita Netto	Dept A	N/A	04/09/2018	24/07/2018

When I unpivoted the table, all blank fields have been removed. How can I keep them so that I can count empty fields as well?

Also, some employees are not required to complete specific training and they will have something like N/A in the date field.

There's 500 employees in total. Table below represents the entire organisation, I will then break it down per dept. I suppose I will count N/As as 'completed' or I will deduct N/As from All Employees so that the true percentage is reflected in the graph.

Something like this.

Training Type	All Employees	In Date	Out of Date	Not Completed	Not Required
Fire	500	400	50	30	20
H&S	500	500	0	0	0
GDPR	500	495	0	0	5

Another difficulty I foresee is that not all courses are valid for only 12 months. Some are 36 months and some are just one off. I'm interested to know how I will be able to overcome this. Maybe by adding conditional column..

How can I achieve this?

Thanks,

Maciek