Skip to Content
Former Member
Oct 29, 2010 at 04:19 PM

Measure Context



I am currently writing a report that separates a group of clients by age group (>1, 1-2, 3-5, etc). The report consists of a crosstab displaying various counts of members eligible for our services.

Since the report is run several months after the reporting period (to allow 90 days for any adjustments to be made), age isn't calculated using the time the report is run. Age is instead calculated based on the date that member terminated services (or by the last date of the reporting period if the member is still active).

A handful of members have terminated services with us, then renewed them at a later date within the reporting period. This gives two calculations for age for these members: one age for their first coverage period, and the other after they have renewed coverage. In these cases, I want only the most recent coverage period to be used, therefore limiting to only the most recent age calculation.

The Problem: Since the report is a crosstab, each cell calculated individually, there is no context by which to group/separate the values. I have tried formulas like:

=Max([Calculated Age]) ForEach([Member ID])

in attempt to get the maximum (thus, the most recent) age for each member. However, this returns the max age out of all the members in the table, regardless of Member ID.

Does anyone have a way to select only the correct age?