Forum Discussion
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 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.
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)
3 Replies
- Selva-Salimi
Solution Sage
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)- Selva-Salimi
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.