Community,
This one has had me scratching my head for a while now and hoping someone can help. Similar to other posts and requirements for "open tickets", I'm looking to find a snapshot of my data at the first of each month, when the data fulfils criteria:
Given a table such as this:
| Ticket Number | Team | Ticket Age | Type | Opened | Closed |
| 1 | A | 17 | High | 01-Apr-19 | 18-Apr-19 |
| 2 | A | 64 | High | 30-Apr-19 | |
| 3 | B | 45 | High | 19-May-19 | |
| 4 | B | 1 | High | 20-Jun-19 | 21-Jun-19 |
| 5 | A | 12 | Medium | 21-Jun-19 | |
| 6 | B | 39 | High | 22-May-19 | 30-Jun-19 |
| 7 | A | 1 | High | 23-Jun-19 | 24-Jun-19 |
| 8 | B | 9 | Low | 24-Jun-19 | |
| 9 | C | 1 | High | 25-Jun-19 | 26-Jun-19 |
| 10 | C | 1 | High | 26-Jun-19 | 27-Jun-19 |
Problem: I need to find, at the start of each month (01-MMM-YY), how many tickets of a certain type were open and over a certain number of days old, per team.
I can find something similar by adding a column to my date table, but this gives me a headline number I can't break down by team, as below:
Count =
CALCULATE (
COUNTA ( Records[Ticket Number] ),
FILTER (Records,
Records[Type] = "High"
&& Records[Opened] <= 'Date'[Date]
&& (Records[Closed] > 'Date'[Date] || ISBLANK ( Records[Closed] ) )
&& Records[Ticket Age] >= 10
)
)
Hopefully I'd be able to find something similar to this output, either as a new table, column, measure etc - but crucially, filterable by Team, because Team is linked to other dimensions I require:
| Team | Month | Count |
| A | 01-Apr | |
| A | 01-May | 1 |
| A | 01-Jun | 1 |
| A | 01-Jul | 2 |
| B | 01-Apr | |
| B | 01-May | |
| B | 01-Jun | 2 |
| B | 01-Jul | 1 |
| C | 01-Apr | |
| C | 01-May | |
| C | 01-Jun | |
| C | 01-Jul | |
I've attempted using SUMMARIZECOLUMNS but haven't had much success, any chance some DAX ninjas are able to assist?
Thanks in advance9i,
Ben
