Forum Discussion

bunny18's avatar
bunny18
Helper I
5 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
    5 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.

amitchandak's avatar
amitchandak
Super User
5 years ago

bunny18 , best is that you use the relative date filter

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

 

If not you can create a rolling, but that will give all data in one place.

Like

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Last 6 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-6 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

 

You might have to follow the approach in this video to get 6 week

https://www.youtube.com/watch?v=duMSovyosXE

  • bunny18's avatar
    bunny18
    Helper I
    5 years ago

    Hi Amit, 

    Appreciate the quick response. I dont see the 'Relative date slicer' in my pbix. I read somewhere, I need to turn on the new look feature which I did, but still I dont see it.