Measure Context

[Former Member]

Hey,

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?

[Former Member]

Thanks for the input, but I was able to figure it out another way.

What I ended up doing was taking the original query, and using a MINUS operator to eliminate any duplicates.

[Former Member]

Try :

=Max([Calculated Age]) ForEach([Date];[Member ID])
=Max([Calculated Age]) ForEach([Member ID]) In Report
=Max([Calculated Age]) ForEach([Member ID] In Report)

For 1st one also use In Report and try it out.

Gracias...!!