Forum Discussion
Helper IHow 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 defects. I want the chart to only show last 6 weeks (or say 12 weeks). How can I achieve it ?
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.
8 Replies
- amitchandak
Super User
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
bunny18Hi 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.
- Greg_Deckler
Community Champion
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-TITHW/m-p/434008 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
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.
