I just wanted to see if any gurus have read and understand my questions, thought process?
This week I found some links to clever tricks to "hide" columns vs. filter them but nothing that hints to my original question(s).
I'll let others answer your "am I understanding this correctly" set of questions. I only wanted to mention that "calculated fields" and "table calculations" are different animals. I think this is where some of your confusion is coming from. Here's the order of execution that Joe and Jonathan came up with awhile back (it may have been refined since then):
Here's the link.
You can see that table calcs are applied late in the process after the query has been returned to Tableau. You are only applying your LOOKUP() table calc to the date-filtered data, so T is behaving as expected.
The solution is to change when your Date Range Filter is applied. By creating this table calculation:
You move the filter from pre-query to post-query. This particular formula is a Wilson Po creation (I think) that allows you to filter out things (months) from the view without filtering them from the calculations. So previous month is being calculated correctly, without having to show all the months. (See attached.)