I have a survey that respondents should complete every 3 months as they go through a year long programme (though they don’t always complete it). In this survey is a health measure that comes out with an average that indicates how ‘healthy’ they are, within certain parameters. Each individual also has a unique ID number that they put in at the start of the survey.

I’m looking for a way to calculate for a dashboard the average change in ‘health’ over time per individual. Once I’ve got the average I’d ideally like to just be able to pop it into a Number Chart or a Gauge Chart.

As far as I can work out, there are a few steps to it (not sure if this is the only way but this is how it makes sense to me - I welcome other suggestions!):

1. Identify responses with the same ID number
2. Identify the earliest and latest of these (this won’t always be within the last calendar quarter as sometimes respondents skip a survey)
3. Calculate the change in ‘health’ between the earliest and latest responses (either as a number or as a percentage)
4. Calculate the average of all the changes

Steps 1 & 2 are the ones I’m most stuck on - I think if those are figured out then I could do 3 & 4.

Any help would be greatly appreciated :)