Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
hasarinfareeth
Frequent Visitor

Backlog Calculation

‎01-18-2025 12:31 PM

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 NumberCreated DateClosed DateStatus
INC0012502/01/202410/01/2024Closed
INC0012615/01/202415/02/2024Closed
INC0012722/02/2024 Open 
INC0012809/03/202420/03/2024Closed
INC0012920/03/202421/04/2024Closed
INC0013026/04/202421/04/2024Closed
INC0013126/05/202401/06/2024Closed
INC0013228/05/202428/05/2024Closed
INC0013321/06/2024 Open
INC0013418/07/202419/08/2024Closed
INC0013505/08/202406/08/2024Closed
INC0013625/08/2024 Open
INC0013719/09/2024 Open
INC0013821/10/202421/11/2024Closed
INC0013919/11/202403/01/2025Closed
INC0014008/12/202403/01/2025Closed
INC0014106/01/2025 Open


The expected result should be as below 

MonthBacklog countRemarks
Feb 20241INC00126 created in Jan but closed in Feb, so backlog for Feb. 
Mar 20241INC00127 created in Feb and is still open, so backlog from Mar. 
Apr 20242INC00127 created in Feb and is still open, so backlog for Apr (since March). INC00129 created in Mar and closed in Apr.
May 20241INC00127 created in Feb and is still open, so backlog for May (since March)
June 20242INC00127 created in Feb and is still open, so backlog for Jun (since March). INC00131 created in May but closed in June.
July 20242 INC00127 created in Feb and is still open. INC00133 created in June and is still open, so 2 backlogs. 
Aug 20243INC00127 and INC00133 is still open since Feb and June. INC00134 is created in July but closed in Aug. 
Sep 20243INC00127 and INC00133 is still open since Feb and June. INC00136 is created in Aug and is still open. 
Oct 20244INC00127, INC00133 and INC00136 iare still open since Feb, June and Aug. INC00137 created in Sep and is still Open.
Nov 20245INC00127, INC00133, INC00136 and INC00137 are still open since Feb, June, Aug and Sep. INC00138 created in Oct but closed in Nov.
Dec 20245INC00127, 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 20256 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
Labels:
Message 1 of 5
463 Views
1 ACCEPTED SOLUTION
AmiraBedh
Super User
Super User

‎01-18-2025 03:16 PM

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

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

View solution in original post

Backlog measure.pbix
Message 2 of 5
410 Views
4 REPLIES 4
ThxAlot
Super User
Super User

‎01-19-2025 12:13 PM

Easy enough,

ThxAlot_0-1737318434349.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LearnAndPractise(Everyday)


)



Backlog.pbix
Message 5 of 5
333 Views
0
hasarinfareeth
Frequent Visitor

‎01-19-2025 10:34 AM

Thanks for your support. It worked for my requirement. 

Message 3 of 5
366 Views
AmiraBedh
Super User
Super User
In response to hasarinfareeth

‎01-19-2025 10:37 AM

Glad to help 😄


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696
Message 4 of 5
361 Views
0
AmiraBedh
Super User
Super User

‎01-18-2025 03:16 PM

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

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696
Backlog measure.pbix
Message 2 of 5
411 Views

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

View All
Top Solution Authors
View All