Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

backlog computation commulative

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 IdStateClosed DateCreated DateTicketKindCreatedDate DatepartProjectName CalcProjectName-TicketClosedDate
102995Active 2/9/2022 8:30Support9-Feb-22Strategic PodStrategic Pod Support
104014Closed8/3/2022 9:432/16/2022 9:43Support16-Feb-22Strategic PodStrategic Pod Support3-Aug-22
110851Closed8/10/2022 23:204/28/2022 8:58Support28-Apr-22Strategic PodStrategic Pod Support10-Aug-22
112044Closed8/24/2022 21:585/10/2022 15:28Support10-May-22Strategic PodStrategic Pod Support24-Aug-22
112106Closed8/10/2022 23:215/11/2022 7:20Support11-May-22Strategic PodStrategic Pod Support10-Aug-22
112445New 5/12/2022 9:10Support12-May-22Strategic PodStrategic Pod Support
112898Closed10/18/2022 9:335/17/2022 9:48Support17-May-22Strategic PodStrategic Pod Support18-Oct-22
114014Closed1/16/2023 9:215/27/2022 17:25Support27-May-22Strategic PodStrategic Pod Support16-Jan-23
114722Closed7/13/2022 22:436/6/2022 11:08Support6-Jun-22Strategic PodStrategic Pod Support13-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?

3 Replies