It's actually not as simple as it sounds. Because you are referencing previous values from the table in your calculation, that field is being computed after most of your filters are run. The trick here will be to filter out previous years in a step that occurs at the same time or after your calculation. Assuming that your are calculating retention using the LOOKUP function (making the field a table calculation), I would suggest filtering out the previous years using a table calculation as well, either using INDEX() or FIRST().
To be more specific, try the following:
- Remove whatever year filter you currently have
- Create a calculated field called FIRST with the following formula: FIRST()
- Add FIRST to your filters, allowing values from 0-2 only
You may also need to adjust the "Compute Using" setting for FIRST to match what you have for your retention calculation.
I hope that helps!
You could try to use the table calc "lookup" to bring back the previous year.
See the attached. "Previous Year Total" is a table calc that brings back the previous year. "Retention" calculates the percentage. How the table calcs are computed (order, direction) are important and dependent on the way you have it set up.
(Note: you dont actually even need the first calculated field you can embed it straight into the retention calculation)
Hopefully this helps.
lookup.twbx 24.2 KB
Thank you.... Got what I needed....