Forum Discussion

bunny18's avatar
bunny18
Icon for Helper I rankHelper I
6 years ago
Solved

How do I get Last 6 weeks bug trend

Hi, We are using Power BI Desktop to create bug metrics/charts with data from Azure DevOps. Below is a chart that shows the total cummulative bugs filed Vs total resolved cummulative Vs open defect...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hello @bunny18 ,

    1.I modified the return format of the three Week(WeekNumberByCreatedDate, WeekNumberByClosedDate, and WeekNumberByResolvedDate) columns in the All bugs incoming table.

    WeekNumberByCreatedDate = If(Year('All incoming Bugs'[Created Date])=2020, "20-W" & FORMAT(WEEKNUM('All incoming Bugs'[Created Date],1),"0#") , "19-W" & WEEKNUM('All incoming Bugs'[Created Date],1)) 
    WeekNumberByClosedDate = if(year('All incoming Bugs'[Closed Date])=BLANK(),BLANK(), If(Year('All incoming Bugs'[Closed Date])=2020, "20-W" & FORMAT(WEEKNUM('All incoming Bugs'[Closed Date],1),"0#") , "19-W" & WEEKNUM('All incoming Bugs'[Closed Date],1)) )
    WeekNumberByResolvedDate = If('All incoming Bugs'[ManipulatedResolvedDate] =BLANK(),BLANK(), If(Year('All incoming Bugs'[ManipulatedResolvedDate])=2020, "20-W" & FORMAT(WEEKNUM('All incoming Bugs'[ManipulatedResolvedDate],1),"0#") , "19-W" & WEEKNUM('All incoming Bugs'[ManipulatedResolvedDate],1)) )

    9.png10.png11.png

    2.I created a calculated column in the FinalOverallData table.

    Num = LEFT([Week],2)&RIGHT([Week],2)

    7.png

    3.I have created a measure to get the last 6 weeks.

    Last 6 weeks =
VAR _week =
    CALCULATE ( MAX ( 'FinalOverallData'[Num] ), ALL ( FinalOverallData ) )
VAR _minWeek = _week - 6
RETURN
    IF (
        _week >= MAX ( 'FinalOverallData'[Num] )
            && _minWeek < MAX ( 'FinalOverallData'[Num] ),
        1
    )

    4.At, I added the measure of the last 6 weeks to these three measures.

    Open = IF([Last 6 weeks]=1,[TotalCumm]-[TotalResolved]- [TotalClosed])
    TotalCumm = IF([Last 6 weeks]=1,CALCULATE(SUm(FinalOverallData[TotalByCreatedDate]),FILTER(ALLSELECTED(FinalOverallData),FinalOverallData[RowIndex] <= Max(FinalOverallData[RowIndex]) )))
    TotalResolvedOrClosedOrRejectedOrRemoved = IF([Last 6 weeks]=1,CALCULATE(SUm(FinalOverallData[Rejected]) +SUm(FinalOverallData[Closed])  +SUm(FinalOverallData[Resolved]) +SUm(FinalOverallData[Removed]),FILTER(ALLSELECTED(FinalOverallData),FinalOverallData[RowIndex] <= MAX(FinalOverallData[RowIndex]))))

    5.The result is as follows.

    8.png

    More details can be found here.

    Best regards

    Stephen Tao

    If this post helps,then consider Accepting it as the solution to help other members find it faster.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi bunny18 ,

 

You can create a measure like so.

Measure =
VAR _week =
    CALCULATE ( MAX ( 'Calendar'[weekNum] ), ALL ( 'Calendar' ) )
VAR _minWeek = _week - 6
RETURN
    CALCULATE (
        SUM ( 'Table'[Values] ),
        FILTER (
            'Calendar',
            'Calendar'[weekNum] > _minWeek
                && 'Calendar'[weekNum] <= _week
        )
)

 

The result is as follows.

 

 

You can check more details from here.

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello @bunny18 ,

    1.I modified the return format of the three Week(WeekNumberByCreatedDate, WeekNumberByClosedDate, and WeekNumberByResolvedDate) columns in the All bugs incoming table.

    WeekNumberByCreatedDate = If(Year('All incoming Bugs'[Created Date])=2020, "20-W" & FORMAT(WEEKNUM('All incoming Bugs'[Created Date],1),"0#") , "19-W" & WEEKNUM('All incoming Bugs'[Created Date],1)) 
    WeekNumberByClosedDate = if(year('All incoming Bugs'[Closed Date])=BLANK(),BLANK(), If(Year('All incoming Bugs'[Closed Date])=2020, "20-W" & FORMAT(WEEKNUM('All incoming Bugs'[Closed Date],1),"0#") , "19-W" & WEEKNUM('All incoming Bugs'[Closed Date],1)) )
    WeekNumberByResolvedDate = If('All incoming Bugs'[ManipulatedResolvedDate] =BLANK(),BLANK(), If(Year('All incoming Bugs'[ManipulatedResolvedDate])=2020, "20-W" & FORMAT(WEEKNUM('All incoming Bugs'[ManipulatedResolvedDate],1),"0#") , "19-W" & WEEKNUM('All incoming Bugs'[ManipulatedResolvedDate],1)) )

    9.png10.png11.png

    2.I created a calculated column in the FinalOverallData table.

    Num = LEFT([Week],2)&RIGHT([Week],2)

    7.png

    3.I have created a measure to get the last 6 weeks.

    Last 6 weeks =
VAR _week =
    CALCULATE ( MAX ( 'FinalOverallData'[Num] ), ALL ( FinalOverallData ) )
VAR _minWeek = _week - 6
RETURN
    IF (
        _week >= MAX ( 'FinalOverallData'[Num] )
            && _minWeek < MAX ( 'FinalOverallData'[Num] ),
        1
    )

    4.At, I added the measure of the last 6 weeks to these three measures.

    Open = IF([Last 6 weeks]=1,[TotalCumm]-[TotalResolved]- [TotalClosed])
    TotalCumm = IF([Last 6 weeks]=1,CALCULATE(SUm(FinalOverallData[TotalByCreatedDate]),FILTER(ALLSELECTED(FinalOverallData),FinalOverallData[RowIndex] <= Max(FinalOverallData[RowIndex]) )))
    TotalResolvedOrClosedOrRejectedOrRemoved = IF([Last 6 weeks]=1,CALCULATE(SUm(FinalOverallData[Rejected]) +SUm(FinalOverallData[Closed])  +SUm(FinalOverallData[Resolved]) +SUm(FinalOverallData[Removed]),FILTER(ALLSELECTED(FinalOverallData),FinalOverallData[RowIndex] <= MAX(FinalOverallData[RowIndex]))))

    5.The result is as follows.

    8.png

    More details can be found here.

    Best regards

    Stephen Tao

    If this post helps,then consider Accepting it as the solution to help other members find it faster.

    • bunny18's avatar
      bunny18
      Icon for Helper I rankHelper I
      6 years ago

      @v-stephen-msft Hello MSFT team, really appreciate the response. Thanks a ton for your support.

      The solution works perfectly as expected.