The finish dates don't make much sense to me. For example, for event # 3107344 the start date is in August but the finish date is in October. That period spans more than 31 days - would the event have been considered past due in the month of September, even though the "finish date" was not yet reached? If so, what does the finish date represent?
Anyways, ignoring the finish dates, based on your description above an event was past due for a particular month if
- The event started prior to the end of the month --> [Start Date] <= [End of Month Date]
- The event was still "open" by the end of the month --> [Start Date] + [Days Open] > [End of Month Date]
- The number of days between the end of the month and the start date are greater or equal to 31 --> [End of Month Date] - [Start Date] >= 31
That's not the correct Tableau syntax, but it should give you an idea of how to set up the calculated fields.
As for the data, I would recommend creating a new data set with [Month Name] and [End of Month Date], cross join it with your data, then count the number of events meeting the three criteria above.
Thank you for the help. I'm going to take your suggestion and create a reference table containing the end of month date.
Finished Date = Closed.
I only care about Events that are Past Due (not closed), so that is where the Finish Date comes into play.
I'll let you know how it turns out. Thanks again!