How do I get Last 6 weeks bug trend

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

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

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

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.

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.