Forum Discussion
Set Maximum Quarter with Slicer (Include Latest Entry up to Selected Quarter)
I have the following dataset of employee testing submissions and a slicer to specify yearly quarters (e.g., 2018 Q1). Pic 1
Each employee has annual testing, and may need to submit remedial work based on test scores (i.e., 100 to 70 = pass; <70 = fail). Additionally, if the remedial work has a failing grade, an Improvement Plan must be submitted annually. Of course, not everyone submits the required documents (pic 2).
Pic 2
The current slicer will show results only for the selected quarter, however, I need to get results for the selected quarter AND the look back to the last submission to get a current status for each employee (pic 3).
Pic 3In this case, looking at 2019 Q1, AAAA will have a status of Fail (from looking back to the last submission made during 2018 Q3. BBBB and CCCC will have a status of Fail. DDDD will have a status of Pass.
For any given quarter, we should be able to get a total count individually of each type of status for that quarter to include the status of the last submission.
2019 Q1 = 3 Fail, 1 Pass, 1 Missing Documents
2019 Q3 = 2 Fail, 2 Pass, 2 Missing Documents
2020 Q1 = 1 Fail, 3 Pass, 2 Missing Documents
All entries have submission dates from which the quarter is derived as a calculated column. Likewise, the status column is also calculated. I'm not sure how to implement the ability to have the slicer set the last quarter to include in the count AND also look back to the last submission (if needed) to get an overall status for each employee.
2 Replies
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.EmployeeID DocType DateSubmitted Quarter Score Result AAAA Annual Test 1/1/2018 2018 Q1 50 Quarterly Remedial Needed AAAA Remedial 4/1/2018 2018 Q2 45 Annual Improvement Plan Needed AAAA Annual Improvement Plan 4/1/2018 2018 Q2 100 Completed AAAA Remedial 7/1/2018 2018 Q3 50 Annual Improvement Plan Needed BBBB Annual Test 1/1/2019 2019 Q1 65 Quarterly Remedial Needed BBBB Remedial 10/1/2019 2019 Q4 75 Continue Quarterly Remedial BBBB Annual Test 1/1/2020 2020 Q1 80 Met Requirements CCCC Annual Test 1/1/2019 2019 Q1 65 Quarterly Remedial Needed CCCC Remedial 4/1/2019 2019 Q2 80 Continue Quarterly Remedial CCCC Remedial 7/1/2019 2019 Q3 80 Continue Quarterly Remedial DDDD Annual Test 1/1/2019 2019 Q1 95 Met Requirements DDDD Annual Test 1/1/2020 2020 Q1 85 Met Requirements Per the recommendation, this is the input data.
EmployeeID 2018 Q1 2018 Q2 2018 Q3 2018 Q4 2019 Q1 2019 Q2 2019 Q3 2019 Q4 2020 Q1 AAAA Fail Fail Fail Missing Missing Missing Missing Missing Missing BBBB Missing Missing Missing Missing Fail Missing Missing Pass Pass CCCC Missing Missing Missing Missing Fail Pass Pass Missing Missing DDDD Missing Missing Missing Missing Pass N/A N/A N/A Pass This shows the result for each employee for any given quarter, however, if anything is missing (not submitted) the returned status (Pass/Fail +/- Missing Documents) should include a "look-back" to include the last submission made. For example, if looking specifically at 2019 Q3...
EmployeeID 2018 Q1 2018 Q2 2018 Q3 2018 Q4 2019 Q1 2019 Q2 2019 Q3 2019 Q4 STATUS AAAA Fail Fail Fail Missing Missing Missing Missing Missing Fail & Missing Documents BBBB Missing Missing Missing Missing Fail Missing Missing Pass Fail & Missing Documents CCCC Missing Missing Missing Missing Fail Pass Pass Missing Pass DDDD Missing Missing Missing Missing Pass N/A N/A N/A Pass ...employee AAAA would have a status of "Fail" (looking back to the last submission made in 2018 Q3) and "Missing Documents" because nothing was submitted for 2019 Q3.
Employee BBBB would also have a status of "Fail" (looking back to the last submission made in 2019 Q1) and "Missing Documents" because nothing was submitted for 2019 Q3.
Employee CCCC would have a status of "Pass."
Employee DDDD would have a status of "Pass." Because the annual test was passed in 2019 Q1, they are not required to make any quarterly submissions. The status of "Pass" is from looking back to the last submission made in 2019 Q1.
