I have a table A that has a column (opened_date) that is bound to a calendar table
table A also has a column (closed_date)
Data:
ID Date Opened Date Closed
1 Jul-17
2 Jul-17 Aug-17
3 Aug-17 Aug-17
4 Sep-17
5 Oct-17 Oct-17
Then I have a Matrix that provides results by dates (calendar table)
What I wanted to know was how many tickets are open at the end of each month.
For example, at the end of July 2017 there were 2 open, at the end of August 2017 there were 0 open, but at the end of September 2017 there was 1 ticket open ....
So what I want to know is all the tickets that were open at the end of each month (the closed date has to be after the end of the month or else it's still empty)
so far I have this dax function in a measure:
# of Open Prob =
VAR tmpTickets = ADDCOLUMNS('PROD',"Effective Date",IF(ISBLANK(PROB_PROD[CLOSED_AT]),TODAY(),PROB_PROD[CLOSED_AT]))
VAR minDate = FIRSTDATE(PROB_PROD[CLOASED_DATE])
VAR maxDate = LASTDATE(PROB_PROD[OPENED_DATE])
VAR tmpTable =
FILTER(
tmpTickets,
[OPENED_DATE] <= maxDate &&
[Effective Date] >= minDate &&
[CLOASED_DATE] = BLANK()
)
RETURN
COUNTROWS(tmpTable) + 0
But the only thing that the measure returns to me is to say all those who do not have closed date at the end of each month, not counting those that were closed after the month we are viewing in the Matrix
Could someone help me? I'm really grateful, because I've been around this problem for some time.
Best Regards,
J.O.
