Forum Discussion
Help Badly Needed!
Hi,
Thank you for coming here.
I want to see how each 'Key' progress in the National Percentile Rank over the test dates. I need the progress as percentage and values if possible.
My table is below;
| Key | Date of Test | National Percentile Rank |
| 234 | 07/06/2022 | 60 |
| 233 | 09/09/2021 | 68 |
| 234 | 31/05/2023 | 84 |
| 555 | 07/04/2021 | 69 |
| 555 | 04/05/2023 | 85 |
| 789 | 07/04/2021 | 60 |
| 789 | 09/04/2022 | 70 |
| 789 | 10/04/2023 | 80 |
Hi,
Try using this to calculate the %:
Percentile Change =
VAR CurrentTest = MAX('Table'[Date of Test])
VAR PreviousTest = CALCULATE(MAX('Table'[Date of Test]), FILTER('Table', 'Table'[Date of Test] < CurrentTest && 'Table'[Key] = EARLIER('Table'[Key])))
VAR CurrentPercentile = MAX('Table'[National Percentile Rank])
VAR PreviousPercentile = CALCULATE(MAX('Table'[National Percentile Rank]), FILTER('Table', 'Table'[Date of Test] = PreviousTest && 'Table'[Key] = EARLIER('Table'[Key])))
RETURN
IF(ISBLANK(PreviousPercentile), BLANK(), (CurrentPercentile - PreviousPercentile) / PreviousPercentile * 100)
4 Replies
Hi,
Try using this to calculate the %:
Percentile Change =
VAR CurrentTest = MAX('Table'[Date of Test])
VAR PreviousTest = CALCULATE(MAX('Table'[Date of Test]), FILTER('Table', 'Table'[Date of Test] < CurrentTest && 'Table'[Key] = EARLIER('Table'[Key])))
VAR CurrentPercentile = MAX('Table'[National Percentile Rank])
VAR PreviousPercentile = CALCULATE(MAX('Table'[National Percentile Rank]), FILTER('Table', 'Table'[Date of Test] = PreviousTest && 'Table'[Key] = EARLIER('Table'[Key])))
RETURN
IF(ISBLANK(PreviousPercentile), BLANK(), (CurrentPercentile - PreviousPercentile) / PreviousPercentile * 100)Thank you for the response! It is much appreciated.
I am getting this error message;
This expression refers to a Partition object named 'Table[Table]', which has an error.
Any ideas?
replace table with your table name.
PercentileChange =
VAR CurrentTestDate = MAX('YourTableName'[Date of Test])
VAR PreviousTestDate =
CALCULATE(
MAX('YourTableName'[Date of Test]),
FILTER(
'YourTableName',
'YourTableName'[Date of Test] < CurrentTestDate &&
'YourTableName'[Key] = EARLIER('YourTableName'[Key])
)
)
VAR CurrentPercentile = MAX('YourTableName'[National Percentile Rank])
VAR PreviousPercentile =
CALCULATE(
MAX('YourTableName'[National Percentile Rank]),
FILTER(
'YourTableName',
'YourTableName'[Date of Test] = PreviousTestDate &&
'YourTableName'[Key] = EARLIER('YourTableName'[Key])
)
)
RETURN
IF(
ISBLANK(PreviousPercentile),
BLANK(),
(CurrentPercentile - PreviousPercentile) / PreviousPercentile * 100
)
