I was working on this one for a while, and was about to give up, until I came up with a simple solution!
The issue is, you can't filter the dates, because once you filter, that date is "removed" from the data. In other words, it can't look up the previous value because there is no previous value, as you found.
However, the alternate version to a filter is to HIDE that data. In Tableau, a Hide does exactly that - it hides, rather than filters, that value.
I set up a sample workbook here.
The first tab, "Original - with issue", basically replicates your situation, if I understand it correctly. Similar parameter, and a Sales, and Previous Week Sales value. Field "Show Dates" is in the Filters, to show true values only. I'm using field "Week Date" to show each week value.
The second tab, "Fix Step 1" removes that Show Dates filter, so all dates are shown. Then I created two new fields:
- "Show Dates New" works exactly like "Show Dates", but gives a text output of "True" and "False" rather than the boolean so we can reference it in another calculated field.
- "Week Date Fixed" is key here. It will replace the "Show Dates" filter, giving a null value for all not in the range:
IF [Show Dates New]='True' THEN [Week Date] End
(Note you can use this same calculation in any other field you have in your final view you need to hide.)
Then to create the third tab, "Fix Step 2 - After Hide", I just took the view in "Fix Step 1", right-clicked on one of the Null values in Week Date Fixed, and selected "Hide". This should fix it for you!
FilterHidePreviousWeek.twbx.zip 111.2 KB
This is a really good suggestion Catherine but it doesn't completely work for me in my situation. My worksheet is displaying the values as line charts but you can't do this with two date fields like is required in this solution. I verified that I can get all of the data displaying correctly with your method which is good and it seems very close but it charts it with individual points for each week instead of the lines that I require. I've attached an example that has a dashboard with two similar worksheets (one 30 day values and one 60 day values) so you can see a side by side comparison of what I have now (60 day) and what I get with these changes (30 day). I'll continue to try and correct this but so far I can not figure out how to implement this fix, which does do what I want in terms of data, and still keep the line charts.
ExampleDashboard.twbx.zip 96.6 KB
I kept thinking along the lines of hiding the data I didn't want in the current view of the charts instead of filtering it and remember something similar I had done for a running sum in another chart that needed to keep adding but not display outside the date range. The fix was to create a week filter with the formula below to use in the Show Dates and Show Dates End calculated fields so that they work the same for the comparisons as before but now they don't actually filter out the actual data. This is working great and fixes the problem.