Weekly defect inflow/outflow in power bi - Need help

[bunny18]

Hi,

I need to plot a weekly inflow/outflow of defects using data in Azure. My data set has the following  columns :

Defect ID, Created Date, Resolved Date

Using this, I have to create a  Weekly chart (by work week) which will have Total bugs cummulative, Resolved Cummulative, Open as of that week. Attached is the data set and also sample chart that am trying to achieve in Power BI.

https://drive.google.com/file/d/18QpjkMZNVTh0HjgXH9lYqLP_CwUgEYEW/view?usp=sharing

Looking forward for some quick help.

[lbendlin · Accepted solution]

Typically, this pattern is served by a Dates table with an active relationship to one of its dates and an inactive relationship to the other. Measures are then created and, in the second measure, USERELATIONSHIP() is specified to change the relationship dynamically.

Do you have a date table (calendar)? Are the actual date fields Creation Date and Resolved Date or are they datetime fields? Are you looking for a solution without the need for a Dates table?

What is your definition of a week's work?

https://community.powerbi.com/t5/Community-Blog/It-s-the-weekend-Or-is-it/ba-p/1216125

[Anonymous]

Weekly defect inflow/outflow calculation does not seem to work on the data where we don't have any end date . How to make it work for some short set of data .  Ex:- in below table , Status [Analysis/In progress/Comitted:- Inflow] wheres Status [Ready for Integration/ready for review/Re-test :- Outflow] at the same time how to proceed for new defects inflow each week ? any help apreciate in this context .

Id	Status	Creation Date	WEEKNUM
1723064	Ready for Integration	8/31/2023 9:32	35
1717946	Analysis	8/28/2023 9:43	35
1717286	Analysis	8/25/2023 22:02	34
1716472	Analysis	8/25/2023 1:39	34
1709465	Analysis	8/23/2023 1:30	34
1709448	Analysis	8/23/2023 1:20	34
1709311	Analysis	8/22/2023 21:57	34
1707948	Ready for Integration	8/22/2023 10:34	34
1706241	Analysis	8/20/2023 21:53	34
1704453	Analysis	8/17/2023 7:22	33
1704227	Analysis	8/17/2023 3:10	33
1700381	Committed	8/11/2023 10:47	32
1692837	Analysis	8/9/2023 1:44	32
1686467	Re-Test	8/2/2023 10:46	31
1686119	Analysis	8/2/2023 9:20	31
1683907	Analysis	8/1/2023 8:41	31
1681967	Ready for Integration	7/31/2023 9:15	31
1681261	Analysis	7/29/2023 16:47	30
1681260	Committed	7/29/2023 16:22	30
1680134	Analysis	7/27/2023 22:50	30
1678152	Analysis	7/27/2023 3:31	30
1677800	Committed	7/26/2023 21:35	30
1669951	Analysis	7/25/2023 10:05	30
1663141	In Progress	7/23/2023 17:18	30
1663091	Analysis	7/23/2023 16:00	30
1663090	In Progress	7/23/2023 15:22	30
1658606	Ready for Review	7/21/2023 4:33	29
1655819	Analysis	7/20/2023 11:04	29
1655652	Analysis	7/20/2023 10:42	29
1652642	Analysis	7/19/2023 23:53	29
1646310	Ready for Review	7/19/2023 2:26	29
1646291	Ready for Integration	7/19/2023 2:15	29
1646271	Ready for Integration	7/19/2023 2:08	29
1637152	Analysis	7/14/2023 1:59	28
1637141	Analysis	7/14/2023 1:18	28
1637018	Re-Test	7/13/2023 22:24	28
1636268	Ready for Integration	7/13/2023 11:04	28
1636076	Ready for Integration	7/13/2023 9:55	28
1635521	Re-Test	7/13/2023 6:42	28
1635333	Committed	7/13/2023 4:22	28
1635142	Ready for Integration	7/13/2023 1:51	28
1632183	Re-Test	7/12/2023 10:06	28
1623481	Ready for Integration	7/5/2023 8:45	27
1621005	Ready for Review	7/3/2023 5:19	27
1609486	Analysis	6/27/2023 0:59	26
1609485	Committed	6/27/2023 0:55	26
1605492	Re-Test	6/22/2023 5:14	25
1602809	Committed	6/21/2023 1:28	25
1602080	Analysis	6/20/2023 11:25	25
1600975	Re-Test	6/20/2023 1:32	25
1597994	Ready for Review	6/16/2023 11:40	24
1597953	Analysis	6/16/2023 11:20	24
1597301	Ready for Integration	6/16/2023 6:27	24
1596741	Ready for Review	6/16/2023 1:01	24
1596022	Analysis	6/15/2023 13:56	24
1595937	Analysis	6/15/2023 13:12	24
1595911	Committed	6/15/2023 12:59	24
1595743	Ready for Integration	6/15/2023 11:33	24
1595604	Ready for Integration	6/15/2023 10:27	24
1595490	Ready for Integration	6/15/2023 9:54	24
1595187	Committed	6/15/2023 8:37	24
1594481	Ready for Integration	6/15/2023 5:10	24
1594326	Ready for Integration	6/15/2023 4:00	24
1586562	Ready for Integration	6/14/2023 8:57	24
1586471	Ready for Integration	6/14/2023 8:46	24
1578183	Ready for Integration	6/9/2023 6:08	23
1572794	Analysis	6/5/2023 0:27	23
1572205	Committed	6/2/2023 9:18	22
1571945	Ready for Integration	6/2/2023 5:15	22
1570991	Analysis	6/1/2023 8:16	22
1563849	Re-Test	5/31/2023 4:13	22
1561164	Ready for Integration	5/30/2023 6:34	22
1560676	Ready for Integration	5/30/2023 2:47	22
1559839	Ready for Integration	5/28/2023 21:02	22
1559019	Analysis	5/26/2023 1:51	21
1553708	Ready for Review	5/18/2023 6:57	20
1553695	Ready for Integration	5/18/2023 6:52	20
1553526	Ready for Integration	5/18/2023 4:46	20
1548333	Ready for Integration	5/17/2023 8:12	20
1542383	Committed	5/11/2023 10:44	19
1540268	Re-Test	5/9/2023 3:36	19
1540254	Re-Test	5/9/2023 3:23	19
1527204	Ready for Integration	5/1/2023 22:46	18
1524447	Committed	4/26/2023 12:50	17
1524169	Re-Test	4/26/2023 8:33	17
1506515	Re-Test	4/18/2023 2:55	16
1494296	Re-Test	4/14/2023 6:25	15
1467378	Ready for Integration	3/31/2023 10:17	13
1437540	Re-Test	3/13/2023 8:19	11
1436521	Re-Test	3/10/2023 5:50	10

[v-diye-msft]

Hi @bunny18 

I believe you are referring to these similar issues:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364 

https://community.powerbi.com/t5/Desktop/Count-of-Open-cases-over-time/td-p/761764 

https://community.powerbi.com/t5/Desktop/Getting-a-running-total-of-current-open-Tickets/td-p/448431 

https://community.powerbi.com/t5/Desktop/Opened-and-Closed-ticket-count-by-Shift-along-with-Carry-Ov... 

[bunny18]

Hi,

Thankyou! I created a Dates table as follows :

I went through the links that you shared, and tried using the formulas (modified) but it didnt work for me.

Date Table

Based on the attached Data from Azure, I need one chart that shows weekly ((by Year & Week Number)) trend of

Total Cummulative 

Total Resolved Cummulative

Open 

The Bug state in the azure data has multiple states (New, Assigned,Implemented) which will give the 'Open'

And Resolved & Closed (having resolved date) that will be the Total resolved cummulative.

how can I achieve this ?

[v-diye-msft]

Hi @bunny18 

It's hard to imagine the issue you met now, would you mind kindly share the dummy pbix that I can invertigate it and draw it up on it?

You can upload it to the onedrive for business and share the link here. please don't forget to disclose the expected results and remove the confidential info.

[bunny18]

Thank you so much.

I have uploaded the .pbix and also the sample excel data which I currently compute manually for charts. Chart 1 & Chart 2 (in excel) is my desired output from BI data.

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

[v-diye-msft · Accepted solution]

Hi @bunny18 

Firstly, plz add the monthnumber column:

weeknum = WEEKNUM('Table'[Created Date],2)

Then try something like below measures:

Total Cummulative = CALCULATE(SUMX(FILTER(ALL('Table'),[Created Date]<=MAX('Table'[Created Date])&&[weeknum]=MAX('Table'[weeknum])),DISTINCTCOUNT('Table'[Defect ID])))

total open cumulative = CALCULATE(SUMX(FILTER(ALL('Table'),[Created Date]<=MAX('Table'[Created Date])&&[weeknum]=MAX('Table'[weeknum])&&[State]="On hold"),DISTINCTCOUNT('Table'[Defect ID])))

total resolved cumulative = CALCULATE(SUMX(FILTER(ALL('Table'),[Created Date]<=MAX('Table'[Created Date])&&[weeknum]=MAX('Table'[weeknum])&&[State]="Resolved"),DISTINCTCOUNT('Table'[Defect ID])))

 

[lbendlin · Accepted solution]

Typically, this pattern is served by a Dates table with an active relationship to one of its dates and an inactive relationship to the other. Measures are then created and, in the second measure, USERELATIONSHIP() is specified to change the relationship dynamically.

Do you have a date table (calendar)? Are the actual date fields Creation Date and Resolved Date or are they datetime fields? Are you looking for a solution without the need for a Dates table?

What is your definition of a week's work?

https://community.powerbi.com/t5/Community-Blog/It-s-the-weekend-Or-is-it/ba-p/1216125

[bunny18]

The dates are as is from Azure DevOps report and I convert them into Week Number using the DAX formula in BI.

It's actually datetime field.

I do not have calendar table and so would prefer to have a solution without the need for dates table.

[lbendlin]

Let me rephrase:  This will not work without a Dates table that has contiguous dates and has a clear designation of "week number"  based on your company's logic (across year and quarter boundaries!)  Using the DAX formula for week numbers is very risky.

[bunny18]

Okay noted. So, will create the dates table. Could you please help provide a solution based on dates table.