Forum Discussion

hasarinfareeth's avatar
hasarinfareeth
Frequent Visitor
1 year ago
Solved

Backlog Calculation

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 ...
Need Help
  • AmiraBedh's avatar
    AmiraBedh
    1 year ago

    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       
        )
    )