Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Cumulative DistinctCount of open Backlogs

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!!

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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_NumberM_DefectResolutionIdM_WorkitemStatusWorkitemChangeTimestampWorkitemAssetStateWorkitemIsClosedWorkitemIsDeleted
      Bug:1-<none>9/29/2013 10:57:51 PM64falsefalse
      Bug:1-<none>10/14/2013 7:47:04 PM64falsefalse
      Bug:1-<none>11/12/2013 5:59:41 AM64falsefalse
      Bug:1-<none>1/28/2014 6:17:32 AM64falsefalse
      Bug:1-<none>3/12/2014 2:53:22 AM64falsefalse
      Bug:1-<none>4/1/2014 1:01:35 AM64falsefalse
      Bug:1-<none>4/1/2014 1:01:44 AM64falsefalse
      Bug:1-<none>4/1/2014 5:48:20 AM64falsefalse
      Bug:1-Available4/1/2014 5:51:02 AM64falsefalse
      Bug:1-Available4/1/2014 6:25:25 AM64falsefalse
      Bug:1-Available4/15/2014 4:58:04 AM64falsefalse
      Bug:1-Available4/15/2014 5:20:18 AM64falsefalse
      Bug:1-Available4/23/2014 12:45:22 AM64falsefalse
      Bug:1-Available5/2/2014 5:32:18 AM64falsefalse
      Bug:1-Available6/15/2014 11:15:56 PM64falsefalse
      Bug:1-Available6/27/2014 4:19:52 AM64falsefalse
      Bug:1-Available6/29/2014 11:49:04 PM64falsefalse
      Bug:1-Available7/28/2014 3:13:15 AM64falsefalse
      Bug:1-Available7/28/2014 3:14:49 AM64falsefalse
      Bug:1-Available8/6/2014 7:58:39 AM64falsefalse
      Bug:1-Available5/22/2015 7:32:28 AM64falsefalse
      Bug:1-Available5/22/2015 7:33:04 AM64falsefalse
      Bug:1-Available6/28/2015 7:23:57 PM64falsefalse
      Bug:1-Available8/14/2015 12:47:03 AM64falsefalse
      Bug:1-Available10/28/2015 3:25:55 AM64falsefalse
      Bug:1-Available1/4/2016 9:51:16 AM64falsefalse
      Bug:1-Available7/1/2016 8:20:17 AM64falsefalse
      Bug:1-Available8/9/2016 5:58:03 AM64falsefalse
      Bug:1-Available10/4/2016 7:37:17 AM64falsefalse
      Bug:1-Available11/30/2016 7:00:57 AM64falsefalse
      Bug:1-Available12/7/2016 1:52:08 AM64falsefalse
      Bug:1-Available12/7/2016 2:10:32 AM64falsefalse
      Bug:1-Available12/20/2016 7:53:31 AM64falsefalse
      Bug:1-Available1/17/2017 4:37:20 AM64falsefalse
      Bug:1-Available2/8/2017 1:32:42 AM64falsefalse
      Bug:1-Available3/6/2017 7:25:52 AM64falsefalse
      Bug:1-Available3/8/2017 1:03:05 AM64falsefalse
      Bug:1-Available3/8/2017 1:03:35 AM64falsefalse
      Bug:1-Available3/8/2017 1:27:29 AM64falsefalse
      Bug:1-Available3/13/2017 12:35:38 AM64falsefalse
      Bug:1-Available3/22/2017 5:01:54 AM64falsefalse
      Bug:1-Available5/10/2017 11:35:04 PM64falsefalse
      Bug:1-Available7/3/2017 1:17:42 AM64falsefalse
      Bug:1-Available7/25/2017 6:06:40 AM64falsefalse
      Bug:1-Available7/31/2017 3:46:22 AM64falsefalse
      Bug:1-Available8/8/2017 6:50:06 AM64falsefalse
      Bug:1-Available8/9/2017 4:13:37 AM64falsefalse
      Bug:10-<none>6/28/2016 5:07:13 AM64falsefalse
      Bug:10-<none>5/31/2017 1:08:09 AM64falsefalse
      Bug:10-Progressing8/9/2017 2:10:58 AM64falsefalse
      Bug:10-Testing8/9/2017 3:50:37 AM64falsefalse

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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.