Cumulative Backlog Calculation Issue

[rahberrizvi110]

Hi ,

I am working on Incident management dashboard where I want to add and show the unresolved ticket from previous month (In Progress and Pending) to  the following month. Ex. If May has 4 tickets , 3 resolved in May and one could not be resolved May and it gets added to the June, if there are already 4 tickets in June then it becomes 5 (May-1 + June-4).

Thanks in advance!

[v-veshwara-msft]

Hi @rahberrizvi110 ,
Thanks for posting in Microsoft Fabric Community.

To address your requirement of tracking unresolved tickets from previous months and combining them with new ones in the current month, I reproduced this using sample data that includes CreatedDate, ResolvedDate, and Status columns.

I created a simple Date table and added measures to calculate monthly new tickets, carried over backlog based on unresolved status at the start of the month, total tickets handled in the month, and those that remain unresolved at the end of the month. 

Sample data used:

Measures used:

NewTicketsThisMonth = 
CALCULATE (
    COUNTROWS (IncidentTable),
    FILTER (
        ALL (IncidentTable),
        MONTH (IncidentTable[CreatedDate]) = MONTH (MAX('Date'[Date])) &&
        YEAR (IncidentTable[CreatedDate]) = YEAR (MAX('Date'[Date]))
    )
)

CarriedOverBacklog = 
CALCULATE (
    COUNTROWS (IncidentTable),
    FILTER (
        ALL (IncidentTable),
        IncidentTable[CreatedDate] < DATE(YEAR(MAX('Date'[Date])), MONTH(MAX('Date'[Date])), 1) &&
        (
            ISBLANK(IncidentTable[ResolvedDate]) ||
            IncidentTable[ResolvedDate] >= DATE(YEAR(MAX('Date'[Date])), MONTH(MAX('Date'[Date])), 1)
        )
    )
)

TotalTicketsThisMonth = 
[NewTicketsThisMonth] + [CarriedOverBacklog]

UnresolvedThisMonth = 
CALCULATE (
    COUNTROWS (IncidentTable),
    FILTER (
        ALL (IncidentTable),
        IncidentTable[CreatedDate] <= MAX('Date'[Date]) &&
        (
            ISBLANK(IncidentTable[ResolvedDate]) ||
            IncidentTable[ResolvedDate] > MAX('Date'[Date])
        )
    )
)

Output(Matrix visual):

Please let us know if this meets your requirements or if you’re looking for a different approach. You may also find the following related threads helpful:

Solved: DAX formula for Monthly Cumulative Backlog Ticket ... - Microsoft Fabric Community

Solved: Backlog Calculation - Microsoft Fabric Community

Hope this helps. Please reach out for further assistance.

Please consider sharing some sample data according to your requirements or sample .pbix without sensitive data to assist better.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you.

Please find attached .pbix for reference.

[rahberrizvi110]

Thanks for your response @v-veshwara-msft . Could we bifurcate the issues by previous months? Ex- If we hover July then it should show total tickets in July which includes 3 Backlogs from May and 5 Backlogs from June. I need this for Tooltips page.


I appreciate your response.

[v-veshwara-msft · Accepted solution]

Hi @rahberrizvi110 ,

Thanks for your follow-up and clarification.

To address the requirement of bifurcating the backlog issues by previous months in the tooltip, I reproduced the scenario using the same sample structure as before and added a disconnected helper table to group the CreatedDate into month buckets.

Then, using a tooltip page and a measure that calculates how many of those earlier tickets were still unresolved at the start of the selected (hovered) month, I was able to get the expected breakdown.

For example, when hovering over July 2025, the tooltip now displays:

Measure used:

BacklogCountByCreatedMonth = 
VAR HoveredDate = SELECTEDVALUE('Date'[Date])
VAR HoveredMonthStart = DATE(YEAR(HoveredDate), MONTH(HoveredDate), 1)
VAR CurrentCreatedMonth = SELECTEDVALUE('CreatedMonth'[CreatedMonthStart])
RETURN
IF (
    NOT ISBLANK(HoveredMonthStart) && NOT ISBLANK(CurrentCreatedMonth),
    CALCULATE (
        COUNTROWS (IncidentTable),
        FILTER (
            ALL (IncidentTable),
            -- Belongs to this Created Month
            DATE(YEAR(IncidentTable[CreatedDate]), MONTH(IncidentTable[CreatedDate]), 1) = CurrentCreatedMonth &&
            -- Still unresolved at start of hovered month
            IncidentTable[CreatedDate] < HoveredMonthStart &&
            (
                ISBLANK(IncidentTable[ResolvedDate]) ||
                IncidentTable[ResolvedDate] >= HoveredMonthStart
            )
        )
    )
)

This matches the intent of tracking how many unresolved tickets from earlier months (created in May and June) are still active at the beginning of July.

Please let us know if this aligns with your intended outcome or if you would like to take a different approach for tooltip design.

Hope this helps. Please reach out for further assistance.

Thank you.

[v-veshwara-msft]

Hi @rahberrizvi110 ,
Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.
Thank you.