Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Yiannis_N
Frequent Visitor

DAX formula for Monthly Cumulative Backlog Ticket Calculation

Hello,
i have found a response in regards of the calculation of Monthly backlog and I m trying to implement a similar measure on my Power BI reports and i
 think the method described of solving the problem is close to the one i m looking for, but in my attempt to download the pbi file and see the calculation, the file is missing.


https://www.linkedin.com/safety/go?url=https%3A%2F%2Fcommunity.fabric.microsoft.com%2Ft5%2FDAX-Comma...


Futhermore some more information on what i need to achieve and how the solution you provided is close but needs something more to be completed.
The Final Output of the report i m trying to implement is the following. The Backlog calculation should provide a cummulative sum of the tickets that remain open (Status = Open) and be added on the following month.

For example: 

On September 2023 Open Tickets  (45) - Closed Tickets (23) + Backlog (1) = 23.
On your previous response regarding the backlog calculation, i notice that the dax formula doesnt take in consideration the previous backlog tickets if on the previous month none was opened or complete.

YearMonthOpened_TicketsClosed_TicketsBacklog
2023June101
2023July331
2023August001
2023September452323
2023October685140
2023November723874
2023December593697
2024January12769155
2024February13564226
2024March104105225
2024April216120321
2024May138237222
2024June55222


A few things on my raw data and tables that i need to do the calculation on.
Table 1 = Jira Raw that contains the following Columns: 

KeyStatusCreatedResolved
Τckt-1043Open3/6/2024 16:41 
Τckt-1042Open3/6/2024 14:305/6/2024 14:30
Τckt-1041Open3/6/2024 12:43 


My calendar Table is called Calendar [Date] that contains year,quarter,month, week and weekday.

Yiannis_N_3-1717748495104.png

 

My relationships look as follows : 

Active relationship between Calendar [Date] and Jira raw [created]
Inactive relatonship between Calendar [Date] and Jira raw [Resolved] 

Yiannis_N_2-1717748449126.png

 

 

 

As far as my calculations :

 

Opened_Tickets = if(ISBLANK(CALCULATE(COUNT(Jira_Raw[Key]), USERELATIONSHIP('Calendar'[Date],Jira_Raw[Created]))), 0, CALCULATE(COUNT(Jira_Raw[Key]), USERELATIONSHIP('Calendar'[Date],Jira_Raw[Created])))
Closed_Tickets = IF(ISBLANK(CALCULATE(COUNT(Jira_Raw[Key]), USERELATIONSHIP('Calendar'[Date],Jira_Raw[Resolved]))),0,CALCULATE(COUNT(Jira_Raw[Key]), USERELATIONSHIP('Calendar'[Date],Jira_Raw[Resolved])))
Backlog = CALCULATE(DISTINCTCOUNT(Jira_Raw[Key]), FILTER(Jira_Raw , Jira_Raw[Created]  <= max('Calendar'[Date]) || not(ISBLANK(Jira_Raw[Created])))) - CALCULATE(DISTINCTCOUNT(Jira_Raw[Key]), FILTER(Jira_Raw , Jira_Raw[Resolved]  <= max('Calendar'[Date]) && not(ISBLANK(Jira_Raw[Resolved]))))


Last but not least i m new to the whole dax and power BI world so any help on achieving the correct calculation of the backlog should be much appreciated.

Best Regards.

1 ACCEPTED SOLUTION
v-jialongy-msft
Community Support
Community Support

Hi @Yiannis_N 

 

First, you need a column to calculate Open_Tickets minus Close_Tickets:

Column = [Opened_Tickets] - [Closed_Tickets]

 

Then you can use the following dax to get the result you want:

Measure = 
var select_date = SELECTEDVALUE('Table'[Year])
RETURN
SELECTEDVALUE('Table'[Column])+SUMX(FILTER(ALL('Table'),'Table'[Year]<select_date),'Table'[Column])

 

Result:

vjialongymsft_0-1719470391878.png

 

 

 

 

Best Regards,

Jayleny

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-jialongy-msft
Community Support
Community Support

Hi @Yiannis_N 

 

First, you need a column to calculate Open_Tickets minus Close_Tickets:

Column = [Opened_Tickets] - [Closed_Tickets]

 

Then you can use the following dax to get the result you want:

Measure = 
var select_date = SELECTEDVALUE('Table'[Year])
RETURN
SELECTEDVALUE('Table'[Column])+SUMX(FILTER(ALL('Table'),'Table'[Year]<select_date),'Table'[Column])

 

Result:

vjialongymsft_0-1719470391878.png

 

 

 

 

Best Regards,

Jayleny

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

lbendlin
Super User
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors