backlog computation commulative

[Anonymous]

Hi,

I'm trying to compute the backlog of tickets commulatively. Logic should be:

"backlog = prev month backlog + created tickets this month - closed tickets this month"

Work Item Id	State	Closed Date	Created Date	TicketKind	CreatedDate Datepart	ProjectName Calc	ProjectName-Ticket	ClosedDate
102995	Active		2/9/2022 8:30	Support	9-Feb-22	Strategic Pod	Strategic Pod Support
104014	Closed	8/3/2022 9:43	2/16/2022 9:43	Support	16-Feb-22	Strategic Pod	Strategic Pod Support	3-Aug-22
110851	Closed	8/10/2022 23:20	4/28/2022 8:58	Support	28-Apr-22	Strategic Pod	Strategic Pod Support	10-Aug-22
112044	Closed	8/24/2022 21:58	5/10/2022 15:28	Support	10-May-22	Strategic Pod	Strategic Pod Support	24-Aug-22
112106	Closed	8/10/2022 23:21	5/11/2022 7:20	Support	11-May-22	Strategic Pod	Strategic Pod Support	10-Aug-22
112445	New		5/12/2022 9:10	Support	12-May-22	Strategic Pod	Strategic Pod Support
112898	Closed	10/18/2022 9:33	5/17/2022 9:48	Support	17-May-22	Strategic Pod	Strategic Pod Support	18-Oct-22
114014	Closed	1/16/2023 9:21	5/27/2022 17:25	Support	27-May-22	Strategic Pod	Strategic Pod Support	16-Jan-23
114722	Closed	7/13/2022 22:43	6/6/2022 11:08	Support	6-Jun-22	Strategic Pod	Strategic Pod Support	13-Jul-22

However, in the above example, March 2022 is null instead of having 2 from the feb 2022 backlog. I think I am just missing something to make it work. I'm not sure how.

This is the code I am using atm.

Backlog-filtered =

var em = ENDOFMONTH('Calendar Dim'[Date])

var teams = VALUES(BusinessExecutiveDashboard[ProjectName-Ticket])

var created = filter(ALL(BusinessExecutiveDashboard),BusinessExecutiveDashboard[CreatedDate Datepart]<=em && BusinessExecutiveDashboard[ProjectName-Ticket] in teams)

var closed = filter(ALL(BusinessExecutiveDashboard),coalesce(BusinessExecutiveDashboard[ClosedDate],em+1)<=em && BusinessExecutiveDashboard[ProjectName-Ticket] in teams)

return COUNTROWS(created)-countrows(closed)

can somebody help me?

[Anonymous]

@amitchandak Hi thanks for replying! I updated the description with the table structure.

[amitchandak · Accepted solution]

@Anonymous , can you share table structure?

check if this can help

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

Thanks this solution is legit.