Help Badly Needed!

[imo3220]

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

[Shravan133 · Accepted solution]

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)

[imo3220]

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?

[Shravan133]

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
)

[imo3220]

Ahh I done that, one was missing. Thank you!