Cumulative DistinctCount of open Backlogs

[Anonymous]

Hi Guys,

I have the following table

My idea is to make a "cumulative distinctcount" in order to check how many open backlog bugs exist.
To know if a  bug was closed or still open is given by the column isOpen, 1 is Open and -1 is Closed.

My end goal is to produce the following a kind of graph like this one. (When a bug is closed, it does not accumulate any longer)

Unfortunatel, the one I have is the following:

Which is wrong. I am using the following measures:

OpenBug:=CALCULATE(DISTINCTCOUNT(ABB[M_Workitem_Number]),FILTER('ABB','ABB'[IsOpen]=1&&ABB[LastRecordMonth]=1))

ClosedBug:=CALCULATE(DISTINCTCOUNT(ABB[M_Workitem_Number]),FILTER('ABB','ABB'[IsOpen]=-1&&ABB[LastRecordMonth]=1))

OpenBacklog:=CALCULATE([openbug]-[Closedbug],FILTER(ALL(DimDate),MAX(DimDate[DateKey])>=DimDate[DateKey]))

And the only difference  between the first graph shown and the next is the &&ABB[LastRecordMonth]=1).

But for this analysis I want to grab only the last line recorded per month, so I can see the latest status of the Bug.

But these measures work when a specific Bug is filtered.

It is not garanteed that a bug will have 1 line per month, so I dont know if blank dates, could be a problem.
But from what I see in this single example below, it does not seem the case, as this bug did not row for March

and it still brings the 1 (openbug) from the previous month. 

This is a case of a bug created in February, that was closed

on April.

Hope you guys can help me out!

Thanks!!

[Greg_Deckler]

Can you post sample data that can be copied and pasted into an Enter Data query? I'm not sure I understand the FILTER statement on your backlog calculation.

[Anonymous]

Sure thing.
Here follows the dataset.
In case you want, the formula for isOpen:

=if(ATC[WorkitemAssetState]=64,
if(ATC[WorkitemIsClosed]=FALSE,
if(ATC[WorkitemIsDeleted]=FALSE,
if(ATC[M_WorkitemStatus]IN{"","Reviewing","Available","Research","Progressing","Testing","Resolved","<none>"},
if(ATC[M_DefectResolutionId]IN {"-","<Unknown>","Resolution:1","Resolution:7"},1,-1)
,-1)
,-1)
,-1)
,-1)

and the Last Record of the month

=RANKX(FILTER(ATC,ATC[M_Workitem_Number]=EARLIER(ATC[M_Workitem_Number])&&ATC[YearMonthNumber]=EARLIER(ATC[YearMonthNumber])),ATC[WorkitemChangeTimestamp],,DESC)
=ATC[YearMonthNumber]&ATC[LastRecordMonth]

Datasample:

Thanks a lot for trying

M_Workitem_Number	M_DefectResolutionId	M_WorkitemStatus	WorkitemChangeTimestamp	WorkitemAssetState	WorkitemIsClosed	WorkitemIsDeleted
Bug:1	-	<none>	9/29/2013 10:57:51 PM	64	false	false
Bug:1	-	<none>	10/14/2013 7:47:04 PM	64	false	false
Bug:1	-	<none>	11/12/2013 5:59:41 AM	64	false	false
Bug:1	-	<none>	1/28/2014 6:17:32 AM	64	false	false
Bug:1	-	<none>	3/12/2014 2:53:22 AM	64	false	false
Bug:1	-	<none>	4/1/2014 1:01:35 AM	64	false	false
Bug:1	-	<none>	4/1/2014 1:01:44 AM	64	false	false
Bug:1	-	<none>	4/1/2014 5:48:20 AM	64	false	false
Bug:1	-	Available	4/1/2014 5:51:02 AM	64	false	false
Bug:1	-	Available	4/1/2014 6:25:25 AM	64	false	false
Bug:1	-	Available	4/15/2014 4:58:04 AM	64	false	false
Bug:1	-	Available	4/15/2014 5:20:18 AM	64	false	false
Bug:1	-	Available	4/23/2014 12:45:22 AM	64	false	false
Bug:1	-	Available	5/2/2014 5:32:18 AM	64	false	false
Bug:1	-	Available	6/15/2014 11:15:56 PM	64	false	false
Bug:1	-	Available	6/27/2014 4:19:52 AM	64	false	false
Bug:1	-	Available	6/29/2014 11:49:04 PM	64	false	false
Bug:1	-	Available	7/28/2014 3:13:15 AM	64	false	false
Bug:1	-	Available	7/28/2014 3:14:49 AM	64	false	false
Bug:1	-	Available	8/6/2014 7:58:39 AM	64	false	false
Bug:1	-	Available	5/22/2015 7:32:28 AM	64	false	false
Bug:1	-	Available	5/22/2015 7:33:04 AM	64	false	false
Bug:1	-	Available	6/28/2015 7:23:57 PM	64	false	false
Bug:1	-	Available	8/14/2015 12:47:03 AM	64	false	false
Bug:1	-	Available	10/28/2015 3:25:55 AM	64	false	false
Bug:1	-	Available	1/4/2016 9:51:16 AM	64	false	false
Bug:1	-	Available	7/1/2016 8:20:17 AM	64	false	false
Bug:1	-	Available	8/9/2016 5:58:03 AM	64	false	false
Bug:1	-	Available	10/4/2016 7:37:17 AM	64	false	false
Bug:1	-	Available	11/30/2016 7:00:57 AM	64	false	false
Bug:1	-	Available	12/7/2016 1:52:08 AM	64	false	false
Bug:1	-	Available	12/7/2016 2:10:32 AM	64	false	false
Bug:1	-	Available	12/20/2016 7:53:31 AM	64	false	false
Bug:1	-	Available	1/17/2017 4:37:20 AM	64	false	false
Bug:1	-	Available	2/8/2017 1:32:42 AM	64	false	false
Bug:1	-	Available	3/6/2017 7:25:52 AM	64	false	false
Bug:1	-	Available	3/8/2017 1:03:05 AM	64	false	false
Bug:1	-	Available	3/8/2017 1:03:35 AM	64	false	false
Bug:1	-	Available	3/8/2017 1:27:29 AM	64	false	false
Bug:1	-	Available	3/13/2017 12:35:38 AM	64	false	false
Bug:1	-	Available	3/22/2017 5:01:54 AM	64	false	false
Bug:1	-	Available	5/10/2017 11:35:04 PM	64	false	false
Bug:1	-	Available	7/3/2017 1:17:42 AM	64	false	false
Bug:1	-	Available	7/25/2017 6:06:40 AM	64	false	false
Bug:1	-	Available	7/31/2017 3:46:22 AM	64	false	false
Bug:1	-	Available	8/8/2017 6:50:06 AM	64	false	false
Bug:1	-	Available	8/9/2017 4:13:37 AM	64	false	false
Bug:10	-	<none>	6/28/2016 5:07:13 AM	64	false	false
Bug:10	-	<none>	5/31/2017 1:08:09 AM	64	false	false
Bug:10	-	Progressing	8/9/2017 2:10:58 AM	64	false	false
Bug:10	-	Testing	8/9/2017 3:50:37 AM	64	false	false

[Anonymous]

If you guys know a way I can do a cumulative sum across time where it stops aggregating once the bug is closed it would be amazing.

[v-yuezhe-msft]

@Anonymous,

Could you please share all rows of your table? ClosedBug measure returns Blank based on the above sample data.

In addition, create a new table using DAX below and create relationship between the new table and DimDate table, then try to calculate OpenBacklog and check if it returns expected result.

Table = CALCULATETABLE(ATC,FILTER(ATC,ATC[LastRecordMonth]=1))

Regards,