Backlog Calculation

[hasarinfareeth]

Hi 

I have a tickets table with the columns - Ticket Number, Ticket Status, Created Date and Closed Date. I have a Date table which has the continuous date from first ticket created date to last ticket created date.  Sample raw data is given below. I wanted to calculate the backlog for each month including bth closed and open tickets. I have tried with various options but I wanted it to be acheived through DAX. 

Sample Data:

Ticket Number	Created Date	Closed Date	Status
INC00125	02/01/2024	10/01/2024	Closed
INC00126	15/01/2024	15/02/2024	Closed
INC00127	22/02/2024		Open
INC00128	09/03/2024	20/03/2024	Closed
INC00129	20/03/2024	21/04/2024	Closed
INC00130	26/04/2024	21/04/2024	Closed
INC00131	26/05/2024	01/06/2024	Closed
INC00132	28/05/2024	28/05/2024	Closed
INC00133	21/06/2024		Open
INC00134	18/07/2024	19/08/2024	Closed
INC00135	05/08/2024	06/08/2024	Closed
INC00136	25/08/2024		Open
INC00137	19/09/2024		Open
INC00138	21/10/2024	21/11/2024	Closed
INC00139	19/11/2024	03/01/2025	Closed
INC00140	08/12/2024	03/01/2025	Closed
INC00141	06/01/2025		Open

The expected result should be as below 

Month	Backlog count	Remarks
Feb 2024	1	INC00126 created in Jan but closed in Feb, so backlog for Feb.
Mar 2024	1	INC00127 created in Feb and is still open, so backlog from Mar.
Apr 2024	2	INC00127 created in Feb and is still open, so backlog for Apr (since March). INC00129 created in Mar and closed in Apr.
May 2024	1	INC00127 created in Feb and is still open, so backlog for May (since March)
June 2024	2	INC00127 created in Feb and is still open, so backlog for Jun (since March). INC00131 created in May but closed in June.
July 2024	2	INC00127 created in Feb and is still open. INC00133 created in June and is still open, so 2 backlogs.
Aug 2024	3	INC00127 and INC00133 is still open since Feb and June. INC00134 is created in July but closed in Aug.
Sep 2024	3	INC00127 and INC00133 is still open since Feb and June. INC00136 is created in Aug and is still open.
Oct 2024	4	INC00127, INC00133 and INC00136 iare still open since Feb, June and Aug. INC00137 created in Sep and is still Open.
Nov 2024	5	INC00127, INC00133, INC00136 and INC00137 are still open since Feb, June, Aug and Sep. INC00138 created in Oct but closed in Nov.
Dec 2024	5	INC00127, INC00133, INC00136 and INC00137 are still open since Feb, June, Aug and Sep. INC00139 created in Nov but closed in Jan 2025 (after Dec 2024)
Jan 2025	6	INC00127, INC00133, INC00136 and INC00137 are still open since Feb, June, Aug and Sep. INC00139 created in Nov but closed in Jan 2025. INC00140 created in Dec but closed in Dec 2024.

Was using the below which works for open ticket, but struggling to acheive the overall number including open and closed tickets. 

Cummulative Open  Tickets Carried forward = CALCULATE(COUNT('Open Tickets'[TicketNumber]), FILTER(ALL('Open Tickets'),'Open Tickets'[Carried Forward Month] <= MAX('Open Tickets'[Carried Forward Month]))) -- Carried Forward Month = Created Date with month +1

Any help here would be really useful and appreciated. 

Thanks

Fareeth

[ThxAlot]

Easy enough,

[hasarinfareeth]

Thanks for your support. It worked for my requirement. 

[AmiraBedh]

Glad to help 😄

[AmiraBedh · Accepted solution]

I created a CC FirstBacklogMonthNumeric in the Tickets to get the first month a ticket contributes to the backlog (the month after creation) and if the month calculation overflows (like December + 1 → January of the next year :

FirstBacklogMonthNumeric = 
IF(
    MONTH([Created Date]) = 12,
    (YEAR([Created Date]) + 1) * 100 + 1, 
    YEAR([Created Date]) * 100 + MONTH([Created Date]) + 1
)

And another CC to get the last month the ticket is active (closed or still open).

LastActiveMonthNumeric = 
IF(
    ISBLANK([Closed Date]) || [Status] = "Open",
    999912, 
    YEAR([Closed Date]) * 100 + MONTH([Closed Date])
)

and then a measure :

Backlog Count = 
VAR CurrentMonth = MAX('Date'[YearMonthNumeric]) 
RETURN
    CALCULATE(
        COUNTROWS('Tickets'),
        FILTER(
            'Tickets',
            'Tickets'[FirstBacklogMonthNumeric] <= CurrentMonth && 
            'Tickets'[LastActiveMonthNumeric] >= CurrentMonth       
        )
    )