Measure compares client's value to most recent previous value (improved, not improved, same)

[ScottGunsaullus]

Sample data:

For each row and client [PATID], I need to calculate 3 things: 

1. Was there a previous [total_score] and if so,
2. What was the previous [total_score]?
3. What was the difference between the [total_score] and the previous [total_score]?

Zero is a valid score.  Lower scores are better.  So, a negative difference would indicate improvement.

[Selva-Salimi · Accepted solution]

Hi @ScottGunsaullus 

you can write these 3 columns :

Prev_assessment = if (calculate(max('Your_table'[assessment_date]) , FILTER('Your_table' , 'Your_table'[PATID]=EARLIER('Your_table'[PATID]) && 'Your_table'[assessment_date] < EARLIER('Your_table'[assessment_date]))) <> Blank() , "Yes" , "No")

Prev_total_score =

var prev_date = calculate(max('Your_table'[assessment_date]) , FILTER('Your_table' , 'Your_table'[PATID]=EARLIER('Your_table'[PATID]) && 'Your_table'[assessment_date] < EARLIER('Your_table'[assessment_date])))

return

CALCULATE(max('Your_table'[total_score]) , filter('Your_table','Your_table'[assessment_date] = prev_date

&& Your_table[PATID]=EARLIER(Your_table[PATID])

))

difference = 'Your_table'[total_score] - 'Your_table'[Prev_total_score]

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.

[Selva-Salimi · Accepted solution]

Hi @ScottGunsaullus 

you can write these 3 columns :

Prev_assessment = if (calculate(max('Your_table'[assessment_date]) , FILTER('Your_table' , 'Your_table'[PATID]=EARLIER('Your_table'[PATID]) && 'Your_table'[assessment_date] < EARLIER('Your_table'[assessment_date]))) <> Blank() , "Yes" , "No")

Prev_total_score =

var prev_date = calculate(max('Your_table'[assessment_date]) , FILTER('Your_table' , 'Your_table'[PATID]=EARLIER('Your_table'[PATID]) && 'Your_table'[assessment_date] < EARLIER('Your_table'[assessment_date])))

return

CALCULATE(max('Your_table'[total_score]) , filter('Your_table','Your_table'[assessment_date] = prev_date

&& Your_table[PATID]=EARLIER(Your_table[PATID])

))

difference = 'Your_table'[total_score] - 'Your_table'[Prev_total_score]

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.

[ScottGunsaullus · Accepted solution]

@Selva-Salimi 

I had to make a minor change to the second column. CALCULATE needs to consider both PATID and assessment_date.

I could not have solved this without your help.  Thanks again,

Prev_total_score =

var prev_date = calculate(max('Your_table'[assessment_date]) , FILTER('Your_table' , 'Your_table'[PATID]=EARLIER('Your_table'[PATID]) && 'Your_table'[assessment_date] < EARLIER('Your_table'[assessment_date])))

return

CALCULATE(max('Your_table'[total_score]) , ALLEXCEPT ( 'Your_table', 'Your_table'[PATID]), 'Your_table'[assessment_date] = prev_date)

[Selva-Salimi]

@ScottGunsaullus 

thanks to pointing that out. you are right, one filter was missed. I have updated my previous response. I would appriciate to mark it as solution to help others find it quickly.