Forum Discussion

rahberrizvi110's avatar
rahberrizvi110
Frequent Visitor
1 year ago
Solved

Cumulative Backlog Calculation Issue

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!

Need Help
  • v-veshwara-msft's avatar
    v-veshwara-msft
    1 year ago

    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.

4 Replies

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    1 year ago

    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's avatar
      rahberrizvi110
      Frequent Visitor
      1 year ago

      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's avatar
        v-veshwara-msft
        Community Support
        1 year ago

        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.