You could try an IF-THEN-END statement, to get the length of stay in "day"; something like this:
On your view, notice that when Grant is: "State BRS", there is no value (not counted)
Attached .twbx file for reference
Let me know if this helps
YH Anonimizer-Copy.twbx 301.3 KB
This clearly demonstrates my wrinkle. On Ken Doll 14. You're totaling all Federal RHY and HOPE bednights.
My question from here is, is there a ways to break up the total to show and total for two stays.
1) The Federal RHY stay from 8/1 to 8/7 totals 6 days. That's a stay.
2) The Federal RHY and HOPE stay from 9/20 to 9/25 totals 4. That's a stay (because the stay is consecutive across Federal RHY and Hope grant types).
You could create a group with those 2 types of grant, however, because we are using both dates the granularity on the view is on that level.
Perhaps you find this article interesting Tableau Doctor: 1 Column, 2 Dates? Use Custom SQL | Tableau Software , it can give you more ideas on how your data can be modeled when working with dates.
Hope this helps!