Dealing with multiple date fields always tends to jack up logic, which is a typical situation to run into with data that represents date ranges.
There is no single answer to this question, so much as a number of different ways to handle it, each with their own disadvantages. The "best" may be a SQL-based solution, because SQL specifically can handle complex relationships between tables that result in various outcomes, including limiting rows, duplicating rows, introducing nulls, etc, which can be exactly what you need. (e.g. join to a calendar table, where join criteria is calendar date is between the two date fields on data table) It is possible to write SQL against an Excel source, when you connect to the file, hit the caret next to "open" and open with legacy connection, then on the Data menu, there is a Convert to SQL command, and you can treat your Excel file like a database, which is pretty cool. If you want to try that and you are not seeing the "Legacy" option, you probably have 64 bit Excel and Tableau, and you just need to run out and install the Microsoft Access driver on your machine, and it will show up. (free: Download Microsoft Access Database Engine 2010 Redistributable from Official Microsoft Download Center )
If SQL is not your deal, I would understand. Another option is to create a calculated field that evalutes at a row level against a parameter inputs. The row level calculation will simply check if [UserDateInput1] is between [Period Start] and [Period Finish] OR [UserDateInput2] is between [Period Start] and [Period Finish]. At a row level, if either of the dates in question is within the range of the row, the row will evaluate to true. Then you throw that calculation on your filter. It's not precise, because you will pick up Periods that extend beyond your dates, but that is the level of detail within the workbook.
I'll stop there and let others throw in their 2¢
You can pivot those "Slice" columns.
In the attached I did Edit Data source on your Sheet3 data source. Then I did ctrl-click on the 7 "Slice" columns, which highlights all 7. Then right click and select "Pivot".
Now you get two new column names in your data source, and they replace your 7 "Slice" columns.
In the attached I made a sheet (I just selected NICK since he is at the top of "Show Data".) You can see what it looks like in the sheet.
From there I created a calc that extracts the number value from the "SliceN" value that the pivot creates. I used this number to do a DATEADD to build the new date values you need. (See [Make a date] calc.) )
Now you have a complete date range to run on an axis. Your user can select a date, and you'll just give them [Make a date] to select on.
Hours by Time Period A.twbx 131.1 KB
This is very cool! But will work only with Excel or other text sources, right? I am actually connecting to a SQL server database, but changed it so that I could mask the data. I appreciate the help!
Thanks Justin. I am using custom SQL for my data source, but I am still new-ish to SQL. I've not been able to figure out how to solve with SQL without causing myself issues in the data source, but I will see what I can do with these suggestions and let you know what I find, thank you!
Pivot will work on your SQL data source too.
I misread your original post in regard to the meaning of those "Slice" columns. I was envisioning the range of Start/Finish on each row to be different.
Joe's solution works great, and will work against any source. Ragged dateframes are more complicated, which is what I had in mind.