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...
Need Help
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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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,