Welcome to the forums! It's a great place with lots of people eager to help -- Tableau employees, customers, and partners.
I've posted a workbook that demonstrates how to do this. The trick is to use a table calculation as the filter.
When a field or row-level calculation is used as a filter, the filter will be calculated at the data source and table calculations will be applied after the data has returned to the Tableau data engine. As you saw, this results in the table calculation not taking into account filtered values.
However, when you use a table calculation as the filter, all the data needed is returned from the data source, the table calculations are computed, and then the table calc filters are applied. That means that the table calculations will be performed for all the data and not just a filtered subset.
Please let me know if you have any questions -- I'd be happy to answer them!
Thank you for the response and advice!
I have one more question regarding this topic. Is it possible to make the table calculations a global filter instead of a regular filter? I have another worksheet that is built on the same concept but on a different granularity (using Effective date instead of Date), so I couldn't add it into the same sheet without multiplying the rows. However, the year/month filter in the example above would be the driving factor, so I want to carry those filters into the new sheet (Sheet 2).
Sheet 1 is from the example above
Sheet 2 is the one where I want the Month/Year filter from Sheet 1 to carry over
Granularity tab is to show you that each Date have multiple Effective Dates