Get previous iteration from dev ops

[Anonymous]

So i am building a stacked chart the will result in showing bugs from a previous iteration and issues from a current iteration on the same chart.  I have calculated everything i believe i need to show bugs and issues for the current iteration fine, i just need a way to calculate bugs from the previous iteration.  i thought about creating an index on the iterations and then somehow calculating bugs from the previous iteration that equals index -1?  Im coming from a sql background that is where my mind is taking me.   

for example 

_01_01 has 13 bugs

_02_01 has 7 bugs

_03_01 has 22 bugs

when i filter by the iteration date on the chart for _03_01 i want to show 7 bugs.  

but i am also showing _3_01 issues that are not the previous iteration.  

hope this makes sense.

[Anonymous]

I guess i need to putmore info out on what im looking for.  I have attached a screenshot of what i want the final result to be and what i am using to calculate the bugs.  I am just missing what i need to add to get the bug calculation for the previous iteration date.  and these dates are not consecultive daily.  they are created dates.  for example 3/4/2023 would have a previous date of 2/20/2023

[parry2k]

@Anonymous Check this video on my YT channel which I did a year ago, and that will get you going. Comparing sales with previous day sales should be easy - Power BI - YouTube

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️

[parry2k]

@Anonymous you need to add a date table in your model, mark it as a date table, set a relationship with your bug/issue table on the date column and then add following measure:

Count = CALCULATE ( COUNTROWS ( BugIssueTable ), DATEADD ( 'Date Table'[Date], -1, DAY ) ) 

To visualize, use the date column from date table, item type from bug/issue table and the count measure , and this will do it

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️

[Anonymous]

After tinkering around, i dont think this will work for what im looking for.  the dates are not days apart, the dates are based off of project length so they could be 01/14/2023, next date could be 3/3/2023 and so on.  

in my attached screenshot, i have been trying with no avail to use a date table and index it, joining it to my main table.  the date table looks like my screenshot.  i thought somehow i might be able to get the bugs for 4/25/2023 to show up on 03/03/2023 with something like 'where index = index -1"

[Anonymous]

ah yeah that seems simple enough.  i appreciate it!

[Anonymous]

Sure, here is an example ive mocked up.  Basically i want to show previous iteration date bugs on current iteration.

[Ashish_Mathur]

Hi,

Please find attached the PBI file.

Hope this helps.

[Anonymous]

appreciate you looking at this.  this would work if the dates were days apart but unfortunantly the dates are spread out over the year.  thats why i was thinking more along the lines of using a date table and adding an index.  im just having trouble figuring out how to show where the index would equal -1 basically to show the previous date.

[Ashish_Mathur]

That is incorrect.  Study my solution again.

[parry2k]

@Anonymous It will be easier if you share some data. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490