How do I get Last 6 weeks bug trend

[bunny18]

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 defects. I want the chart to only show last 6 weeks (or say 12 weeks). How can I achieve it ?

[v-stephen-msft]

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.

[bunny18]

I tried the formula, although I get last 6 weeks only chart, but the cummulative bug count gets reset and calculated only from last 6 weeks. Whereas, I dont want the data to reset/change, only the visualization to be for last 6 weeks.

https://drive.google.com/drive/folders/1ZwiV022tY8IaKU4Kup09BX5ABEc0JJTr?usp=sharing

[v-stephen-msft · Accepted solution]

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.

[bunny18]

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

The solution works perfectly as expected.

[Greg_Deckler]

@bunny18 - There is a Rolling Week calculation in the quick measure gallery. https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Weeks/m-p/391694#M128

Also, a Rolling Average quick measure built into Power BI Desktop.

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Anonymous]

Create a new column on the date field calculating whether the date in the last 6 weeks then Yes else No, use DateAdd or DateDiff function with Today Dax Function. use this new column as a visual level filter on your required visual.

[amitchandak]

@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]

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.