Overlaps per month

[Patrick_BT]

Hi all,
I have a table with tickets and I need to find out how many blocker tickets were open at the same time per month.

TicketID	Created	Resolved	Priority
1	01.04.2021	01.04.2021	Blocker
2	01.04.2021	03.04.2021	Standard
3	05.04.2021	31.06.2021	Blocker
4	07.04.2021	11.04.2021	Blocker
5	15.05.2021	20.05.2021	Blocker

So the result should be:

• 04.2021: 3 tickets (ID 3, 4 & 5)
• 05.2021: 2 tickets (ID 3 & 5)
• 06.2021: 1 ticket (ID 3)

Found some measures for overlapping values, but they always count per ticket and not by month, or the ticket always counts for the month it was created, but should be counted in every month it was open.
Thanks in advance,
Greetings Patrick

[CNENFRNL · Accepted solution]

Excel worksheet formula is powerful enough to solve this simple question,

[BA_Pete]

Hi @Patrick_BT ,

You'll need to create a calendar dimension table. There's plenty of examples online either in DAX or Power Query (recommended). Your calendar table will need at leaste Date and Month/Year (MM.yyyy).

Once you have that, the basic measure structure for your counts would be something like this:

_openTickets =
VAR __maxDate = MAX(calendar[date])
VAR __minDate = MIN(calendar[date])
RETURN
CALCULATE(
  DISTINCTCOUNT(yourTable[TicketID]),
  yourTable[Priority] = "Blocker",
  FILTER(
    yourTable,
    yourTable[Created] <= __maxDate
    && yourTable[Resolved] >= __minDate
  )
)

Use calendar[YearMonth] and [_openTickets] in your visual.

Pete

[Patrick_BT]

Hi Pete,

thanks, I have created the calendar table and the measure already looks good.

Is it possible to extend it so that not all tickets from e.g. 04.2021 are counted, but only the maximum number of concurrently open tickets?

Ticket ID 1 is currently also counted although there was no overlap with the other tickets in April.

Many greetings
Patrick