generally, i want to be able to count how many times some MEASURE 1 (at the row level) exceeds some MEASURE 2 (at the table level).
my specific instance is this:
i have a database of events. each row of the dataset represents a unique EVENT REF. each event has a START DATE and an END DATE. if all events were only 1 day long, it would be a simple matter of using COUNTD(EVENT REF) to say how many events are occuring each day. however, when there are mutiple-day events (i.e. where START DATE <> END DATE), then those events will occur on days that a COUNTD(EVENT REF) would miss (because they are running on a day that isn't associated with a START DATE). in effect, they 'spillover'.
so, what i want to do is to look at each START DATE (at the row level) and find how many events (records) have both an earlier START DATE as well as a later END DATE...to find how many events are 'already in progress' on that START DATE as opposed to starting on that START DATE. in other words, i need to find:
COUNTD(EVENT REF)* where (THIS START DATE > ANY START DATE) AND (THIS START DATE =< ANY END DATE).
*this could just as well be SUM(NUMBER OF RECORDS)
to warm up, i started with a basic case of SUM(INT(START DATE > #AN EXPLCIT DATE#), but i can only get it to work for, well, an explicit date (e.g. 1 NOV 2017). but instead of an explicit date, i need it to work for the START DATE at the row level.
here is a screenshot of the basic setup (x.date is just a copy of START DATE which i may attempt to replace with a calendar scaffold down the road):
a packaged workbook using tabluau 10.3.1 is attached, as well.
I believe the following Tableau Community document contains the answer to the above question regarding counting dates in a span of two dates. Calculating No. of Days between 2 Dates In terms of Years, Months, Days