We have a set of certification information along with the user details as mentioned in Table 1. Based on certain certification rules (mentioned in the Certification Rule Section), we need to report the certification status of all the users with respect to year as shown in Table 3.
Can someone please help me out with the DAX formulation for this? Thank you in advance
Table 1
Input Description:
- Column A: email ID of the employee
- Column B: Employee role
- Column C: Employee joining date
- Column 😧 Employee leaving date (if applicable)
- Column E: Name of the quiz
- Column F: Certification attempt date
- Column E: Certification result
- Column H: Status of the employee
Certification Rule:
- Role 1 must complete every year all of the Quiz 1, Quiz 2, Quiz 3, and Quiz 4 to ensure their certification is completed.
- Role 2 must complete every year any one of the Quiz 5 or Quiz 6 to ensure their quiz status is completed.
- Current certification status is computed, however, for the past quarter certification status is computed on the last day of the quarter
- If the all required certification is done in one year, then the status = completed, If the certification is partially done, then the status is = incomplete, certification is not passed at all the status = not started, and if the employee is left before the last day of the quarter, then status = not applicable
Eg: Lets say 1 employee (email id john@test.com) with Role 1, joined on 20-Dec-2020, completed Quiz 1, Quiz 2, Quiz 3 on 20 Feb 2021, and Quiz 4 on Q4 2021. After that he completes Quiz 1 & Quiz 2 in Q2 2022, and no quiz completed after that. And he is still an active employee. So the output should be as follows:
Table 2
User Email | Year | Certification Status Q1 | Certification Status Q2 | Certification Status Q3 | Certification Status Q4 |
john@test.com | 2020 | Not applicable | Not applicable | Not applicable | Not started |
User email | Year | Certification Status Q1 | Certification Status Q2 | Certification Status Q3 | Certification Status Q4 |
john@test.com | 2021 | Incomplete | Incomplete | Incomplete | Completed |
john@test.com | 2022 | Incomplete | Incomplete | Incomplete | Incomplete |
john@test.com | 2023 | Not started | Not started | Not started | Not started |
- If an employee leaves at the mid of the year, output table will have his email id till end of that year, next year his name will not appear.
- Certification status reset to not started at the beginning of every year (1st January)
Therefore, the output should have the following matrix in the power query.
Table 3
