Finding the a column value of the last two most recent dates

[pablopablo]

Hi,

I have some data which has an assessment rating with an assessment date for several centres. Each centre may have 1 or more assessments. 

I am trying to get the assessment rating for the most recent assessment date, then the assessment rating for the first data prior to the most recent date so I can compare the two assessments.

Any ideas who I could achieve this?

[Anonymous]

@pablopablo Please create a measure to get the current and previous rating

Latest = 
VAR _date = MAX('Table'[Date])
VAR LatestRating = CALCULATE(MAX('Table'[Rating]),'Table'[Date]=_date)
VAR _priordate = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<_date))
VAR _priorrating = CALCULATE(MAX('Table'[Rating]),'Table'[Date]=_priordate)
RETURN LatestRating-_priorrating

latest rating is the rating for latest date
then calculate the previous assessment date and rating on previous assessment date

finally comparing current and previous