create graph from daily data of dataset

[bgashok]

I have below two table form Azure and would like to plot two graph.

Graph 1 = Y-axis : No. of work items in close state from the items present at start of sprint date / total no of work items present at start of sprint date.  X-axis : Sprint name

Graph 2 = Y-axis :  No. of work items added after sprint start date /  total no of work items present at start of sprint date. X-axis : Sprint name

Table 1 consists of work item data status for every day.

Table 2 consists of sprint name , start date and end date.  

Table 1: 

Sprint Name	Start Date	End Date
Q2-S1	2-Aug-23	15-Aug- 23
Q2-S2	16-Aug-23	29-Aug-23
Q2-S3	30-Aug-23	12-Sep-23
Q2-S4	13-Sep-23	26-Sep-23
Q2-S5	27-Sep-23	10-Oct-23
Q2-S6	11-Oct-23	24-Oct-23
Q2-S7	25-Oct-23	7-Nov-23
Q3-S1	8-Nov-23	21-Nov-23
Q3-S2	22-Nov-23	5-Dec-23
Q3-S3	6-Dec-23	19-Dec-23
Q3-S4	20-Dec-23	2-Jan-23
Q3-S5	3-Jan-23	16-Jan-23
Q3-S6	17-Jan-23	30-Jan-23

Table 2: below is sample data, it as different teams, differt sprint and lot of workitem

Team	Sprint	Work Item Id	State	Closed Date	Date
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/7/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/8/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/9/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/10/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/11/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/12/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/13/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/14/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/15/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/16/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/17/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/18/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/19/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/20/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/21/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/22/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/23/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/24/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/25/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/26/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/27/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/28/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/29/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/30/2023
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	12/1/2023
Team Y	Q3-S1	76	Develop	null	10/31/2023
Team Y	Q3-S1	76	Develop	null	11/1/2023
Team Y	Q3-S1	76	Develop	null	11/2/2023
Team Y	Q3-S1	76	Develop	null	11/3/2023
Team Y	Q3-S1	76	Develop	null	11/4/2023
Team Y	Q3-S1	76	Develop	null	11/5/2023
Team Y	Q3-S1	76	Develop	null	11/6/2023
Team Y	Q3-S1	76	Ready	null	10/17/2023
Team Y	Q3-S1	76	Ready	null	10/18/2023
Team Y	Q3-S1	76	Ready	null	10/19/2023
Team Y	Q3-S1	76	Ready	null	10/20/2023
Team Y	Q3-S1	76	Ready	null	10/21/2023
Team Y	Q3-S1	76	Ready	null	10/22/2023
Team Y	Q3-S1	76	Ready	null	10/23/2023
Team Y	Q3-S1	76	Ready	null	10/24/2023
Team Y	Q3-S1	76	Ready	null	10/25/2023
Team Y	Q3-S1	76	Ready	null	10/26/2023
Team Y	Q3-S1	76	Ready	null	10/27/2023
Team Y	Q3-S1	76	Ready	null	10/28/2023
Team Y	Q3-S1	76	Ready	null	10/29/2023
Team Y	Q3-S1	76	Ready	null	10/30/2023
Team Y	Q3-S1	51	Develop	null	10/26/2023
Team Y	Q3-S1	51	Develop	null	10/27/2023
Team Y	Q3-S1	51	Develop	null	10/28/2023
Team Y	Q3-S1	51	Develop	null	10/29/2023
Team Y	Q3-S1	51	Develop	null	10/30/2023
Team Y	Q3-S1	51	Develop	null	10/31/2023
Team Y	Q3-S1	51	Develop	null	11/1/2023
Team Y	Q3-S1	51	Develop	null	11/2/2023
Team Y	Q3-S1	51	Develop	null	11/3/2023
Team Y	Q3-S1	51	Develop	null	11/4/2023
Team Y	Q3-S1	51	Develop	null	11/5/2023
Team Y	Q3-S1	51	Develop	null	11/6/2023
Team Y	Q3-S1	51	New	null	10/17/2023
Team Y	Q3-S1	51	New	null	10/18/2023
Team Y	Q3-S1	51	New	null	10/19/2023
Team Y	Q3-S1	51	New	null	10/20/2023
Team Y	Q3-S1	51	New	null	10/21/2023
Team Y	Q3-S1	51	New	null	10/22/2023
Team Y	Q3-S1	51	New	null	10/23/2023
Team Y	Q3-S1	51	New	null	10/24/2023
Team Y	Q3-S1	51	New	null	10/25/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/7/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/8/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/9/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/10/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/11/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/12/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/13/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/14/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/15/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/16/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/17/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/18/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/19/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/20/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/21/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/22/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/23/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/24/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/25/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/26/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/27/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/28/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/29/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/30/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	12/1/2023
Team Y	Q3-S1	89	Develop	null	10/31/2023
Team Y	Q3-S1	89	Develop	null	11/1/2023
Team Y	Q3-S1	89	Develop	null	11/2/2023
Team Y	Q3-S1	89	Develop	null	11/3/2023
Team Y	Q3-S1	89	Develop	null	11/4/2023
Team Y	Q3-S1	89	Develop	null	11/5/2023
Team Y	Q3-S1	89	Develop	null	11/6/2023
Team Y	Q3-S1	89	Ready	null	10/17/2023
Team Y	Q3-S1	89	Ready	null	10/18/2023
Team Y	Q3-S1	89	Ready	null	10/19/2023
Team Y	Q3-S1	89	Ready	null	10/20/2023
Team Y	Q3-S2	89	Closed	2023-11-21T10:45:40.0700000-06:00	11/21/2023
Team Y	Q3-S2	89	Closed	2023-11-21T10:45:40.0700000-06:00	11/22/2023
Team Y	Q3-S2	89	Closed	2023-11-21T10:45:40.0700000-06:00	11/23/2023
Team Y	Q3-S2	89	Closed	2023-11-21T10:45:40.0700000-06:00	11/24/2023
Team Y	Q3-S2	89	Closed	2023-11-21T10:45:40.0700000-06:00	11/25/2023
Team Y	Q3-S2	89	Closed	2023-11-21T10:45:40.0700000-06:00	11/26/2023
Team Y	Q3-S2	89	Closed	2023-11-21T10:45:40.0700000-06:00	11/27/2023
Team Y	Q3-S2	89	Closed	2023-11-21T10:45:40.0700000-06:00	11/28/2023
Team Y	Q3-S2	89	Closed	2023-11-21T10:45:40.0700000-06:00	11/29/2023
Team Y	Q3-S2	89	Closed	2023-11-21T10:45:40.0700000-06:00	11/30/2023
Team Y	Q3-S2	89	Closed	2023-11-21T10:45:40.0700000-06:00	12/1/2023
Team Y	Q3-S2	89	Ready	null	11/1/2023
Team Y	Q3-S2	89	Ready	null	11/2/2023
Team Y	Q3-S2	89	Ready	null	11/3/2023
Team Y	Q3-S2	89	Ready	null	11/4/2023
Team Y	Q3-S2	89	Ready	null	11/5/2023
Team Y	Q3-S2	89	Ready	null	11/6/2023
Team Y	Q3-S2	89	Ready	null	11/7/2023
Team Y	Q3-S2	89	Develop	null	11/8/2023
Team Y	Q3-S2	89	Develop	null	11/9/2023
Team Y	Q3-S2	89	Develop	null	11/10/2023
Team Y	Q3-S2	89	Develop	null	11/11/2023
Team Y	Q3-S2	89	Develop	null	11/12/2023
Team Y	Q3-S2	89	Develop	null	11/13/2023
Team Y	Q3-S2	89	Develop	null	11/14/2023
Team Y	Q3-S2	89	Develop	null	11/15/2023
Team Y	Q3-S2	89	Develop	null	11/16/2023
Team Y	Q3-S2	89	Develop	null	11/17/2023
Team Y	Q3-S2	89	Develop	null	11/18/2023
Team Y	Q3-S2	89	Develop	null	11/19/2023
Team Y	Q3-S2	89	Develop	null	11/20/2023

[bgashok]

Found the solution in another thread! thankyou for the support @lbendlin 

[lbendlin]

Please indicate the year values in your first table.

[bgashok]

Have updated. 

[lbendlin]

see attached

[bgashok]

it's not giving expected results, I want to share pbix file, but unble to upload it here.

Shall I share the table data here. it around 9K records?

[lbendlin]

 https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

[bgashok]

@lbendlin 

for Q2-S7 sprint  start date is 25 Oct and end date is 7 nov.

 on 25 oct:

Team	Sprint	Work Item Id	State	Closed Date	Date
Team Y	Q3-S1	76	Ready	null	10/25/2023
Team Y	Q3-S1	51	New	null	10/25/2023

on 7 Nov: 

Team	Sprint	Work Item Id	State	Closed Date	Date
Team Y	Q3-S1	76	Closed	2023-11-07T11:17:30.9030000-06:00	11/7/2023
Team Y	Q3-S1	89	Closed	2023-11-07T11:17:30.9030000-06:00	11/7/2023
Team Y	Q3-S2	89	Ready	null	11/7/2023

Graph 1 = Y-axis : No. of work items in close state from the items present at start of sprint date (1 )/ total no of work items present at start of sprint date (2 - ignore if any closed task is presnt in count).  X-axis : Sprint name

= 1/2*100 = 50%

[bgashok]

@lbendlin any help?

[lbendlin]

Did you find a way to share a sample pbix or Excel file?

[bgashok]

I am unable to upload, as I am new user. not getting the options for me

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[bgashok]

@lbendlin 

Graph 1: number of work items committed accepted (closed state) / (number of work items planned (closed state need to exlcuded)) e.g: 5/14*100 = 35.71 (only first 5 items are closed and rest of them are newly added)

(14 only items present on 25 Oct which are in not closed state, exclude close state for count) ref sheet 4

Graph 2: number of work items committed accepted (closed state) / (number of work items planned - number of committed work  items explicitly removed) 

Graph 3: number of work items added / number of committed work items e.g: 9/14 = 64.28

below is example of Q3 S1: start date on 25th Oct and end date on 7th Nov

google link:

https://docs.google.com/spreadsheets/d/1NkFs-BnPTnqacRuEtYg5BoSwPOgSzk3n/edit?usp=drive_link&ouid=11...

[lbendlin]

What is the significance of the [Date] column in the RRR table?

[bgashok]

in RRR Table, Date reflects the workitem status on that particular day and would have all calender days for a durtion.

Currenly pulling report of all workitem status on each day for a specific duration, so that we know the historcal infomration of work items on that particalur day for creating graphs.

[lbendlin]

That seems excessive. Have you considered using change tracking instead?