Graph showing Sum of Opened bugs per sprint

[Anonymous]

Hi

I have a Graph showing Opened and Closed bugs in a sprint. However, I'm also interested in showing Sum of Opened bugs per sprint.

Definition:

Sprint: Has a start and end date e.g. Sprint 108, Sprint 109, Sprint 110 etc. (shown on X-axis)

Opened bugs: All bugs with a CreatedDate (of type WorkItemOrigin=BugCreatedInServiceDesk) between start and end date of a sprint i.e. Bugs Opened in a sprint

Closed bugs: All bugs with a ClosedDate (of type WorkItemOrigin=BugCreatedInServiceDesk) between start and end date of a sprint i.e. Bugs Closed in a sprint

Sum of Opened bugs: All newly Opened bugs (in current sprint) + existing Open bugs (from previous sprints) - Closed bugs (However, I want to retain the history i.e. if Sum of Opened bugs in Sprint 116 is 20, then when current sprint becomes 117, 118, 119 etc. it should still show 20 for Sprint 116) i.e. Sum of all open bugs

Example:

Sprint 1:   Opened 4     Closed 2     Sum of Opened 2

Sprint 2:   Opened 8     Closed 0     Sum of Opened 10

Sprint 3:   Opened 2     Closed 6     Sum of Opened 6

Sprint 4:   Opened 3     Closed 5     Sum of Opened 4

Sprint 5:   Opened 11   Closed 13   Sum of Opened 2

My relations are as follows:

My formulas:

Opened = CALCULATE(

                                      DISTINCTCOUNT(

                                      WorkItems[id]);

                                      WorkItems;WorkItems[WorkItemOrigin]="BugCreatedInServiceDesk";

                                      USERELATIONSHIP(WorkItems[fields_SystemCreatedDate];SprintDates[Date])

                                     )

Closed = CALCULATE(

                                       DISTINCTCOUNT(

                                       WorkItems[id]);

                                       WorkItems;WorkItems[WorkItemOrigin]="BugCreatedInServiceDesk";

                                       USERELATIONSHIP(WorkItems[fields_MicrosoftVSTSCommonClosedDate];SprintDates[Date])

                                     )

I have tried the following, but it show the same graph as Opened:

Sum of Opened = VAR ld = LASTDATE(SprintDates[Date])

                              VAR fd = FIRSTDATE(SprintDates[Date])

                              RETURN

                              CALCULATE(

                                                 [Bugs (All)];

                                                 KEEPFILTERS(

                                                 FILTER(

                                                ALL(WorkItems[fields_SystemCreatedDate];WorkItems[fields_MicrosoftVSTSCommonClosedDate]);

                                                WorkItems[fields_SystemCreatedDate] <= ld && ISBLANK(WorkItems[fields_SystemCreatedDate])=FALSE() &&

                                                OR(

                                                WorkItems[fields_MicrosoftVSTSCommonClosedDate] >= fd;

                                                ISBLANK(WorkItems[fields_MicrosoftVSTSCommonClosedDate])

                                                 )

                                                 )

                                                 )

                                                 )

Any idea of how I can achieve this? All inputs are valuable 🙂

Best regards

Martin

[v-lili6-msft]

hi, @Anonymous 

Could you please share a simple sample pbix for us have a test? Just remove all other columns and with little sample data in it.

You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Lin

[Anonymous]

Hi

I have provided a simple sample pbix as requested. I hope it makes sense.

https://1drv.ms/u/s!ApQ3zdo87D7Ia3X8TRAdRijbqhQ?e=3BeKxr

Best regards

Martin

[v-lili6-msft · Accepted solution]

HI, @Anonymous 

Just try this way:

Step1:

Adjust your formula [Closed]

Closed = CALCULATE(
        DISTINCTCOUNT(
            WorkItems[id]),
            WorkItems,WorkItems[Origin]="BugCreatedInServiceDesk",
            USERELATIONSHIP(WorkItems[ClosedDate],SprintDates[Date])
    ,WorkItems[ClosedDate] <>BLANK()
)

Step2:

Create a sum of open measure

Sum of Opened = CALCULATE([Opened],FILTER(ALLSELECTED(SprintDates),SprintDates[Name]<=MAX(SprintDates[Name])))-CALCULATE([Closed],FILTER(ALLSELECTED(SprintDates),SprintDates[Name]<=MAX(SprintDates[Name])))

Result:

Best Regards,

Lin

[Anonymous]

Hi Lin

Thank you so much for you reply and solution. It is much appreciated.

Your solution works perfectly, however I can't seem to understand you formula in step 2. Can you elaborate on why that works? You do something with Opened and then you subtract something with Closed?

Best regards

Martin