Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I've been looking around for solutions both here and /r/PowerBI but I don't think this has been touched on before. With the given dataset below, is it possible to display the most recent available grade based on the selected period? The Period filter is based on a Calendar table, so it's possible to select a Period that doesn't have any data in it or has incomplete data (eg: Q4 2018 onwards).
Name | Period | Rating | TAT | Reports | EvaluationDate |
Name A | Q1 2018 | 1=Poor | 3 | 5 | 3/1/2018 |
Name A | Q2 2018 | 3=Average | 4 | 3 | 6/1/2018 |
Name A | Q3 2018 | 2=Below Average | 2 | 6 | 9/1/2018 |
Name A | Q4 2018 | 2 | 4 | 12/1/2018 | |
Name B | Q1 2018 | 3=Average | 3 | 4 | 3/1/2018 |
Name B | Q3 2018 | 2=Below Average | 2 | 4 | 9/1/2018 |
Name B | Q4 2018 | 5 | 7 | 12/1/2018 | |
Name C | Q1 2018 | 3=Average | 1 | 7 | 3/1/2018 |
Name C | Q2 2018 | 3=Average | 5 | 6 | 6/1/2018 |
Name D | Q2 2018 | 4=Above Average | 3 | 3 | 6/1/2018 |
Name D | Q3 2018 | 3=Average | 5 | 2 | 9/1/2018 |
Name E | Q1 2018 | 2=Below Average | 2 | 3 | 3/1/2018 |
Case 1:
If no periods are selected, display the most recent available Rating. Since the Calendar includes dates in the future (Q4 2018), when you select Q4 2018, it should still display the most recent available Rating. In this case:
Name A = 2 (Q3), Name B = 2 (Q3), Name C = 3 (Q2), Name D = 3 (Q3), Name E = 2 (Q1)
Case 2:
If one period is selected, display only the Ratings for that period. If Q2 is selected, only the following will be displayed:
Name A = 3, Name C = 3, Name D = 4
Case 3:
If more than one period is selected, only the most recent available Rating should be displayed. If Q2 and Q3 are selected:
Name A = 2 (Q3), Name B = 2 (Q3), Name C = 3 (Q2), Name D = 3 (Q3)
I tried the solution from this thread and it only solves the first case. The code below however results in a "EARLIER/EARLIEST refers to an earlier row context which doesn't exist." error.
~Most Recent Grade = CALCULATE( MAX('Grade'[Grade]), FILTER( ALL('Grade'), 'Grade'[EvaluationDate] = EARLIER('Grade'[EvaluationDate], 1) ) )
Anyone know how to get around this?
Hi @olimilo
You may refer to below measure to get measure for case1. Then you may use IF condition to get the different case measures.
RecentDate = CALCULATE ( MAX ( Grade[EvaluationDate] ), FILTER ( ALLEXCEPT ( Grade, Grade[Name] ), Grade[EvaluationDate] <= NOW () ) )
MostRecentRating1 = CALCULATE ( MAX ( Grade[Rating] ), FILTER ( ALLEXCEPT ( Grade, Grade[Name] ), Grade[EvaluationDate] = [RecentDate] ) )
Regards,
Cherie
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |