Below is a canonical method to get the result.
However, it involves a cross join which may not
be feasible if your true data set is too large.
The method is described here:
This actually probably works better with your original datasource
where the open and closed dates were in the same row.
It joins your data table to a lookup table
with one column of month starts and another of month end.
It runs continuously from 1/2013 to 12/2019.
I joined the data to the lookup table using
DATETRUNC('month',[Created Date]) <= DATETRUNC('month',[Month Start])
(this was done to reduce at least some of the joins
could be done the other way with Close and End, not sure which reduces more)
Then I use a Filter on the worksheet to weed out those joins after the close month:
DATETRUNC('month',[Date Closed])>=DATETRUNC('month',[Month End])
So Ticket Status at End of Month is:
IF [Date Closed]>[Month End] THEN "Open"
ELSEIF [Date Closed]<[Month End] THEN "Closed"
And Ticket Age at End of Month is:
IF [Date Closed]>[Month End] THEN
DATEDIFF('day',[Created Date],[Month End])
Please see workbook v10.5 attached in the Forum Thread.
Here is a more comprehensive discussion on a similar subject:
Possibly Pertinent links:
279122tickets.twbx 607.3 KB