Forum Discussion
Solved
Measure compares client's value to most recent previous value (improved, not improved, same)
Sample data: For each row and client [PATID], I need to calculate 3 things: Was there a previous [total_score] and if so, What was the previous [total_score]? What was the difference betw...
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])))returnCALCULATE(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.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])))returnCALCULATE(max('Your_table'[total_score]) , ALLEXCEPT ( 'Your_table', 'Your_table'[PATID]), 'Your_table'[assessment_date] = prev_date)
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
Solution Sage
Solution Sage
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.