Forum Discussion
COUNTIFS (if) dates meet criteria
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
Community 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] )
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