Need Assistance with Calculating Cumulative Backlog in Power BI

[Neeraj_I]

Hello Power BI Community,

I'm currently working on a project where I need to calculate the cumulative backlog of active tickets over time based on the data provided below:
Sample table

Number Created Closed Active?

INC001	2024-01-05	NULL	YES
INC002	2024-01-10	2024-01-20	NO
INC003	2024-02-15	NULL	YES
INC004	2024-03-08	NULL	YES
INC005	2024-04-20	2024-04-25	NO
INC006	2024-05-12	NULL	YES
INC007	2024-06-25	NULL	YES
INC008	2024-07-02	NULL	YES
INC009	2024-08-18	2024-08-20	NO
INC010	2024-09-09	NULL	YES
INC011	2024-10-24	NULL	YES
INC012	2024-11-05	NULL	YES
INC013	2024-12-14	2024-12-20	NO
INC014	2025-01-29	NULL	YES
INC015	2025-02-10	NULL	YES

I also have a separate Date table for the year 2024, which is connected to the date column in the above table.

Currently, the visualization simply subtracts the 'Created' vs 'Closed' incidents for each month and displays the active backlog for that month only. However, I need to calculate the cumulative active backlog for each month, including all previous months. Essentially, the cumulative backlog should add up month by month.

I'm encountering some challenges in implementing this calculation in Power BI. Can anyone provide guidance on how to approach this problem? Any assistance or suggestions on the DAX measures or calculations needed would be greatly appreciated.

Thank you in advance for your help!

[Ashish_Mathur]

Hi,

Based on the sample table that you have shared, show the expected result very clearly in a simple Table format.

[v-cgao-msft · Accepted solution]

Hi @Neeraj_I ,

Please create a calculated colunm first:

YearMonthSort = 'Date'[Year]*12+'Date'[Month Number]

and then please create a new measure:

Active = SUMX(FILTER('Date','Date'[YearMonthSort]<=MIN('Date'[YearMonthSort])),[Created]-[Closed])

Please check the pbix file.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly--How to provide sample data in the Power BI Forum--China Power BI User Group

[MNedix]

Hi,

Have you tried creating a measure like:

Cummulative Log = CALCULATE(DISTINCTCOUNT('Table'[Number]),'Table'[Active?]="Yes")

If this solved your problem then please mark it as the solution so others could see it.