topic DAX formula for Monthly Cumulative Backlog Ticket Calculation in DAX Commands and Tips

DAX formula for Monthly Cumulative Backlog Ticket Calculation

Yiannis_N — Fri, 07 Jun 2024 08:25:01 GMT

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-Commands-and-Tips%2FNeed-help-to-create-DAX-formula-for-monthly-backlog-ticket%2Ftd-p%2F1651032&trk=flagship-messaging-web&messageThreadUrn=urn%3Ali%3AmessagingThread%3A2-OTkwNjEwMTYtOTMwNS00MzJkLThmNDQtMjMzNjUwNTFjYTEzXzAxMw%3D%3D&lipi=urn%3Ali%3Apage%3Ad_flagship3_profile_view_base%3BlGvr8tgdR92f5F9Hpj%2BQRg%3D%3D

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.

Year	Month	Opened_Tickets	Closed_Tickets	Backlog
2023	June	1	0	1
2023	July	3	3	1
2023	August	0	0	1
2023	September	45	23	23
2023	October	68	51	40
2023	November	72	38	74
2023	December	59	36	97
2024	January	127	69	155
2024	February	135	64	226
2024	March	104	105	225
2024	April	216	120	321
2024	May	138	237	222
2024	June	5	5	222

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:

Key	Status	Created	Resolved
Τckt-1043	Open	3/6/2024 16:41
Τckt-1042	Open	3/6/2024 14:30	5/6/2024 14:30
Τckt-1041	Open	3/6/2024 12:43

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

My relationships look as follows :

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

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.

Re: DAX formula for Monthly Cumulative Backlog Ticket Calculation

lbendlin — Fri, 07 Jun 2024 16:16:58 GMT

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-Forum/ba-p/963216

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/1447523

Re: DAX formula for Monthly Cumulative Backlog Ticket Calculation

Anonymous — Thu, 27 Jun 2024 06:40:27 GMT

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:

Best Regards,

Jayleny

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

Re: DAX formula for Monthly Cumulative Backlog Ticket Calculation

Merc_A — Wed, 01 Oct 2025 15:29:23 GMT

Very late to the party here But This issue took a long time for me to solve myself. I put together a Proof PBX file so i wouldn't forget. My initial use case was identical to yours (ticket volume) however my Proof I put together is a more universal example. Historical Employee Headcount. Since both examples provide only a key, start date & end date, the math is the same.

PBX Proof

Here are the measures in the proof and their use case.

1) (Your use case) This counts all active Employees / Tickets over the time period defined by your X-Axis (Yr, Qtr, Mth).

Employee Count During Period = Var EndDatePerVisual = MAX('Calendar'[Dates]) VAR StartdatePerVisual = MIN('Calendar'[Dates]) Var RESULT = CALCULATE( COUNTROWS('Employee Data'), REMOVEFILTERS('Calendar'), 'Employee Data'[Start Date] <= EndDatePerVisual, 'Employee Data'[End Date] > StartdatePerVisual || ISBLANK('Employee Data'[End Date]) ) RETURN RESULT

2) Same as before, but instead of counting all Active items over the month, This one returns a snapshot of the counts as they were at the end of the time period (yr, qtr, month).

Employee Count at End of Period = Var EndDatePerVisual = MAX('Calendar'[Dates]) Var RESULT = CALCULATE( COUNTROWS('Employee Data'), REMOVEFILTERS('Calendar'), 'Employee Data'[Start Date] <= EndDatePerVisual, 'Employee Data'[End Date] > EndDatePerVisual || ISBLANK('Employee Data'[End Date]) ) RETURN RESULT