Forum Discussion

imo3220's avatar
imo3220
Frequent Visitor
2 years ago
Solved

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 tabl...
Need Help
  • Shravan133's avatar
    Shravan133
    2 years ago

    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)

Shravan133's avatar
Shravan133
Super User
2 years ago

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's avatar
    imo3220
    Frequent Visitor
    2 years ago

    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's avatar
      Shravan133
      Super User
      2 years ago

      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's avatar
        imo3220
        Frequent Visitor
        2 years ago

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