Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have issue with a specific topic which I need som help to create a measure to.
So I need to create a measure that visualizes how many issues I have taken into my current work sprint.
The problem is, that if the issue is not solved within the work sprint, the issue will "jump" to the next work sprint.
This causes the problem that I cannot use the data to estimate how "good" we are at taking issues into our work sprint because the data accummulates from previous work sprints.
I need a visual that shows me how many issues I have taken into my work sprint without showing the issues that have "jumped" from work sprint to work sprint.
This will be used in order to help our team to better estimate how many issues we should commit to for each work sprint.
Solved! Go to Solution.
SOLUTION:
Finally I found a solution.
By using the following to find out:
@Anonymous
I think I may have come up with a way (if @mwegener suggestion does not work), albeit rather convoluted (there must be an easier way...)
Firstly I ordered the table in Power Query in ascending order by the Sprint field. I then added and index column:
Then these measures:
1) calculate the minimum index per project in the table:
Min Index per project = CALCULATE(MIN('DataTable'[Index]); ALLEXCEPT('DataTable'; 'DataTable'[Project Key]))
2) Compare this value to the original index. If it is the same, compute 1
equals index? = IF(MIN('DataTable'[Index]) = [Min Index per project]; 1; BLANK())
3) And finally sum the values returned by [equals index?]:
Net Number of projects = SUMX(
SUMMARIZE('DataTable'; 'DataTable'[Sprint]; 'DataTable'[Project Key];
"equals?"; [equals index?]); [equals?])
Which delivers this:
Which I think is what you are looking for...
Proud to be a Super User!
Paul on Linkedin.
Proud to be a Super User!
Paul on Linkedin.
Hi @Anonymous ,
can you group the data by Project.key.ID, Date.created and return the MAX Sprint?
Hi @mwegener - I can group them - but what do you mean by "return the MAX sprint"?
Hi @Anonymous ,
I thought you always wanted to see the highest (MAX) sprint number.
@mwegener- Yes you are right!
I want to see in which sprint the project.key.id (issue) has started and how many times it has "jumped" to next sprints.
Hi @Anonymous ,
can you provide some sample data (as text)?
FYI: https://www.goodly.co.in/copy-paste-data-power-bi/
Sprint,Project.key.ID,Date.created
BIFI Sprint 20,BIBSO-10,2019-02-13T10:02:13.000+0000
Sprint 31,BIBSO-10,2019-02-13T10:02:13.000+0000
Sprint 32,BIBSO-10,2019-02-13T10:02:13.000+0000
Sprint 33,BIBSO-10,2019-02-13T10:02:13.000+0000
Sprint 34,BIBSO-10,2019-02-13T10:02:13.000+0000
Sprint 35,BIBSO-10,2019-02-13T10:02:13.000+0000
Sprint 35,BIBSO-100,2019-11-20T09:39:14.000+0000
Sprint 36,BIBSO-100,2019-11-20T09:39:14.000+0000
Sprint 37,BIBSO-100,2019-11-20T09:39:14.000+0000
Sprint 38,BIBSO-100,2019-11-20T09:39:14.000+0000
Sprint 35,BIBSO-101,2019-11-05T09:54:51.000+0000
Sprint 35,BIBSO-105,2019-12-02T14:11:43.000+0000
Sprint 36,BIBSO-105,2019-12-02T14:11:43.000+0000
Sprint 34,BIBSO-107,2019-12-04T08:22:00.000+0000
Sprint 35,BIBSO-107,2019-12-04T08:22:00.000+0000
Sprint 35,BIBSO-118,2019-12-13T06:56:05.000+0000
Sprint 35,BIBSO-119,2019-12-12T13:19:16.000+0000
Sprint 35,BIBSO-12,2019-08-30T11:00:31.000+0000
Sprint 35,BIBSO-120,2019-12-12T13:20:15.000+0000
Sprint 36,BIBSO-125,2019-12-20T07:51:23.000+0000
Sprint 35,BIBSO-126,2019-12-20T11:00:26.000+0000
Sprint 35,BIBSO-128,2019-12-27T05:46:45.000+0000
Sprint 35,BIBSO-129,2019-12-27T05:47:33.000+0000
Sprint 35,BIBSO-130,2019-12-27T05:47:39.000+0000
Sprint 35,BIBSO-131,2020-01-02T13:38:17.000+0000
Sprint 35,BIBSO-132,2020-01-06T11:26:55.000+0000
Sprint 36,BIBSO-132,2020-01-06T11:26:55.000+0000
Sprint 37,BIBSO-132,2020-01-06T11:26:55.000+0000
Sprint 38,BIBSO-132,2020-01-06T11:26:55.000+0000
Sprint 36,BIBSO-133,2020-01-06T13:17:07.000+0000
Sprint 37,BIBSO-134,2020-01-08T09:01:26.000+0000
Sprint 37,BIBSO-135,2020-01-08T09:03:10.000+0000
Sprint 37,BIBSO-136,2020-01-08T09:04:12.000+0000
Sprint 37,BIBSO-137,2020-01-08T09:04:46.000+0000
Sprint 36,BIBSO-142,2020-01-09T13:11:29.000+0000
Sprint 36,BIBSO-143,2020-01-09T13:13:45.000+0000
Sprint 35,BIBSO-146,2020-01-13T11:09:41.000+0000
Sprint 35,BIBSO-147,2020-01-13T11:11:58.000+0000
Sprint 35,BIBSO-148,2020-01-13T11:14:06.000+0000
Sprint 35,BIBSO-149,2020-01-13T11:14:52.000+0000
Sprint 35,BIBSO-150,2020-01-13T11:15:18.000+0000
Sprint 35,BIBSO-151,2020-01-13T11:16:20.000+0000
Sprint 36,BIBSO-152,2020-01-14T07:42:39.000+0000
Sprint 36,BIBSO-153,2020-01-14T07:43:29.000+0000
Sprint 37,BIBSO-153,2020-01-14T07:43:29.000+0000
Sprint 36,BIBSO-154,2020-01-14T08:18:27.000+0000
Sprint 36,BIBSO-156,2020-01-14T08:48:15.000+0000
Sprint 35,BIBSO-158,2020-01-15T05:38:16.000+0000
Sprint 36,BIBSO-158,2020-01-15T05:38:16.000+0000
Sprint 37,BIBSO-158,2020-01-15T05:38:16.000+0000
Sprint 38,BIBSO-158,2020-01-15T05:38:16.000+0000
Sprint 35,BIBSO-159,2020-01-15T05:39:16.000+0000
Sprint 36,BIBSO-159,2020-01-15T05:39:16.000+0000
Sprint 37,BIBSO-159,2020-01-15T05:39:16.000+0000
Sprint 38,BIBSO-159,2020-01-15T05:39:16.000+0000
Sprint 35,BIBSO-160,2020-01-15T05:39:25.000+0000
Sprint 36,BIBSO-160,2020-01-15T05:39:25.000+0000
Sprint 37,BIBSO-160,2020-01-15T05:39:25.000+0000
Sprint 38,BIBSO-160,2020-01-15T05:39:25.000+0000
Sprint 35,BIBSO-161,2020-01-15T05:39:51.000+0000
Sprint 36,BIBSO-161,2020-01-15T05:39:51.000+0000
Sprint 37,BIBSO-161,2020-01-15T05:39:51.000+0000
Sprint 38,BIBSO-161,2020-01-15T05:39:51.000+0000
Sprint 35,BIBSO-162,2020-01-15T05:40:01.000+0000
Sprint 36,BIBSO-162,2020-01-15T05:40:01.000+0000
Sprint 37,BIBSO-162,2020-01-15T05:40:01.000+0000
Sprint 38,BIBSO-162,2020-01-15T05:40:01.000+0000
Sprint 35,BIBSO-163,2020-01-15T05:40:10.000+0000
Sprint 36,BIBSO-163,2020-01-15T05:40:10.000+0000
Sprint 37,BIBSO-163,2020-01-15T05:40:10.000+0000
Sprint 38,BIBSO-163,2020-01-15T05:40:10.000+0000
Sprint 35,BIBSO-164,2020-01-15T05:40:24.000+0000
Sprint 36,BIBSO-164,2020-01-15T05:40:24.000+0000
Sprint 37,BIBSO-164,2020-01-15T05:40:24.000+0000
Sprint 38,BIBSO-164,2020-01-15T05:40:24.000+0000
Sprint 36,BIBSO-165,2020-01-17T09:50:30.000+0000
Sprint 37,BIBSO-165,2020-01-17T09:50:30.000+0000
Sprint 38,BIBSO-165,2020-01-17T09:50:30.000+0000
Sprint 36,BIBSO-166,2020-01-21T06:31:21.000+0000
Sprint 36,BIBSO-167,2020-01-21T06:51:05.000+0000
Sprint 36,BIBSO-168,2020-01-21T06:51:06.000+0000
Sprint 36,BIBSO-169,2020-01-21T06:54:14.000+0000
Sprint 36,BIBSO-170,2020-01-21T06:54:31.000+0000
Sprint 36,BIBSO-171,2020-01-21T06:55:09.000+0000
Sprint 36,BIBSO-172,2020-01-21T06:55:16.000+0000
Sprint 36,BIBSO-173,2020-01-21T06:55:27.000+0000
Sprint 36,BIBSO-174,2020-01-22T04:27:29.000+0000
Sprint 37,BIBSO-174,2020-01-22T04:27:29.000+0000
Sprint 36,BIBSO-175,2020-01-13T11:18:43.000+0000
Sprint 37,BIBSO-175,2020-01-13T11:18:43.000+0000
Sprint 36,BIBSO-176,2020-01-27T04:31:18.000+0000
Sprint 37,BIBSO-176,2020-01-27T04:31:18.000+0000
Sprint 36,BIBSO-179,2020-01-28T08:26:11.000+0000
Sprint 37,BIBSO-179,2020-01-28T08:26:11.000+0000
Sprint 36,BIBSO-180,2020-01-28T12:08:30.000+0000
Sprint 37,BIBSO-180,2020-01-28T12:08:30.000+0000
Sprint 37,BIBSO-191,2020-01-30T14:47:36.000+0000
Sprint 36,BIBSO-192,2020-01-31T07:02:06.000+0000
Sprint 37,BIBSO-192,2020-01-31T07:02:06.000+0000
Sprint 36,BIBSO-198,2020-01-31T07:22:41.000+0000
Sprint 37,BIBSO-198,2020-01-31T07:22:41.000+0000
Sprint 36,BIBSO-199,2020-01-31T07:23:35.000+0000
Sprint 37,BIBSO-199,2020-01-31T07:23:35.000+0000
Sprint 36,BIBSO-200,2020-01-31T07:23:50.000+0000
Sprint 37,BIBSO-200,2020-01-31T07:23:50.000+0000
Sprint 36,BIBSO-201,2020-01-31T08:14:27.000+0000
Sprint 37,BIBSO-201,2020-01-31T08:14:27.000+0000
Sprint 36,BIBSO-202,2020-01-31T08:14:42.000+0000
Sprint 37,BIBSO-202,2020-01-31T08:14:42.000+0000
Sprint 37,BIBSO-204,2020-02-03T09:38:51.000+0000
Sprint 37,BIBSO-205,2020-02-03T09:39:34.000+0000
Sprint 37,BIBSO-206,2020-02-03T09:39:49.000+0000
Sprint 37,BIBSO-207,2020-02-03T09:40:02.000+0000
Sprint 37,BIBSO-208,2020-02-04T06:49:36.000+0000
Sprint 37,BIBSO-209,2020-02-04T06:51:35.000+0000
Sprint 37,BIBSO-210,2020-02-04T06:51:56.000+0000
Sprint 37,BIBSO-211,2020-02-04T06:52:28.000+0000
Sprint 37,BIBSO-212,2020-02-04T06:52:45.000+0000
Sprint 37,BIBSO-213,2020-02-04T11:05:36.000+0000
Sprint 37,BIBSO-214,2020-02-04T11:21:10.000+0000
Sprint 38,BIBSO-214,2020-02-04T11:21:10.000+0000
Sprint 37,BIBSO-216,2020-02-10T09:13:33.000+0000
Sprint 37,BIBSO-217,2020-02-10T13:17:59.000+0000
Sprint 37,BIBSO-218,2020-02-11T07:41:32.000+0000
Sprint 35,BIBSO-3,2019-07-04T12:31:44.000+0000
Sprint 30,BIBSO-54,2019-09-12T11:12:59.000+0000
Sprint 32,BIBSO-54,2019-09-12T11:12:59.000+0000
Sprint 33,BIBSO-54,2019-09-12T11:12:59.000+0000
Sprint 34,BIBSO-54,2019-09-12T11:12:59.000+0000
Sprint 35,BIBSO-54,2019-09-12T11:12:59.000+0000
BIFI Sprint 20,BIBSO-68,2019-11-04T07:07:09.000+0000
Sprint 31,BIBSO-68,2019-11-04T07:07:09.000+0000
Sprint 32,BIBSO-68,2019-11-04T07:07:09.000+0000
Sprint 33,BIBSO-68,2019-11-04T07:07:09.000+0000
Sprint 34,BIBSO-68,2019-11-04T07:07:09.000+0000
Sprint 35,BIBSO-68,2019-11-04T07:07:09.000+0000
BIFI Sprint 20,BIBSO-69,2019-11-04T07:07:24.000+0000
Sprint 31,BIBSO-69,2019-11-04T07:07:24.000+0000
Sprint 32,BIBSO-69,2019-11-04T07:07:24.000+0000
Sprint 33,BIBSO-69,2019-11-04T07:07:24.000+0000
Sprint 34,BIBSO-69,2019-11-04T07:07:24.000+0000
Sprint 35,BIBSO-69,2019-11-04T07:07:24.000+0000
BIFI Sprint 20,BIBSO-70,2019-11-04T07:08:18.000+0000
Sprint 31,BIBSO-70,2019-11-04T07:08:18.000+0000
Sprint 32,BIBSO-70,2019-11-04T07:08:18.000+0000
Sprint 33,BIBSO-70,2019-11-04T07:08:18.000+0000
Sprint 34,BIBSO-70,2019-11-04T07:08:18.000+0000
Sprint 35,BIBSO-70,2019-11-04T07:08:18.000+0000
Sprint 33,BIEHSM-55,2019-11-21T12:03:19.000+0000
Sprint 34,BIEHSM-55,2019-11-21T12:03:19.000+0000
Sprint 35,BIEHSM-55,2019-11-21T12:03:19.000+0000
Sprint 35,BIEHSM-60,2019-12-16T08:58:48.000+0000
Sprint 36,BIEHSM-61,2020-01-09T13:06:46.000+0000
Sprint 37,BIEHSM-61,2020-01-09T13:06:46.000+0000
Sprint 36,BIEHSM-64,2020-01-24T08:06:40.000+0000
Sprint 37,BIEHSM-64,2020-01-24T08:06:40.000+0000
Sprint 36,BIEHSM-65,2020-01-24T08:07:27.000+0000
Sprint 37,BIEHSM-65,2020-01-24T08:07:27.000+0000
Sprint 36,BIEHSM-66,2020-01-24T08:08:07.000+0000
Sprint 37,BIEHSM-66,2020-01-24T08:08:07.000+0000
Sprint 36,BIEHSM-67,2020-01-24T08:08:33.000+0000
Sprint 37,BIEHSM-67,2020-01-24T08:08:33.000+0000
Sprint 36,BIEHSM-68,2020-01-24T08:08:53.000+0000
Sprint 37,BIEHSM-68,2020-01-24T08:08:53.000+0000
Sprint 36,BIEHSM-69,2020-01-24T08:09:07.000+0000
Sprint 37,BIEHSM-69,2020-01-24T08:09:07.000+0000
Sprint 36,BIEHSM-70,2020-01-24T08:09:23.000+0000
Sprint 37,BIEHSM-70,2020-01-24T08:09:23.000+0000
Sprint 37,BIEN-137,2020-01-31T07:10:11.000+0000
Sprint 37,BIEN-138,2020-01-31T07:10:43.000+0000
Sprint 37,BIEN-139,2020-02-05T13:01:52.000+0000
Sprint 37,BIEN-140,2020-02-05T13:02:04.000+0000
Sprint 37,BIEN-141,2020-02-05T13:02:16.000+0000
Sprint 26,BIHR-192,2019-08-07T12:22:30.000+0000
Sprint 27,BIHR-192,2019-08-07T12:22:30.000+0000
Sprint 37,BIHR-192,2019-08-07T12:22:30.000+0000
Sprint 26,BIHR-201,2019-08-14T08:43:02.000+0000
Sprint 27,BIHR-201,2019-08-14T08:43:02.000+0000
Sprint 37,BIHR-201,2019-08-14T08:43:02.000+0000
Sprint 26,BIHR-202,2019-08-14T08:43:59.000+0000
Sprint 27,BIHR-202,2019-08-14T08:43:59.000+0000
Sprint 37,BIHR-202,2019-08-14T08:43:59.000+0000
Sprint 26,BIHR-203,2019-08-14T08:44:36.000+0000
Sprint 27,BIHR-203,2019-08-14T08:44:36.000+0000
Sprint 37,BIHR-203,2019-08-14T08:44:36.000+0000
Sprint 26,BIHR-204,2019-08-15T10:14:37.000+0000
Sprint 27,BIHR-204,2019-08-15T10:14:37.000+0000
Sprint 37,BIHR-204,2019-08-15T10:14:37.000+0000
Sprint 35,BIHR-221,2019-11-12T12:23:55.000+0000
Sprint 35,BIHR-230,2019-12-18T08:23:19.000+0000
Sprint 36,BIHR-230,2019-12-18T08:23:19.000+0000
Sprint 26,BIHR-231,2020-02-07T08:12:20.000+0000
Sprint 27,BIHR-231,2020-02-07T08:12:20.000+0000
Sprint 37,BIHR-231,2020-02-07T08:12:20.000+0000
Sprint 30,BIIT-75,2019-08-28T12:20:58.000+0000
Sprint 31,BIIT-75,2019-08-28T12:20:58.000+0000
Sprint 32,BIIT-75,2019-08-28T12:20:58.000+0000
Sprint 33,BIIT-75,2019-08-28T12:20:58.000+0000
Sprint 34,BIIT-75,2019-08-28T12:20:58.000+0000
Sprint 35,BIIT-75,2019-08-28T12:20:58.000+0000
Sprint 36,BIIT-75,2019-08-28T12:20:58.000+0000
Sprint 36,BIIT-78,2020-01-09T08:20:35.000+0000
Sprint 37,BIIT-78,2020-01-09T08:20:35.000+0000
Sprint 38,BIIT-78,2020-01-09T08:20:35.000+0000
Sprint 30,BIIT-79,2020-01-16T11:19:08.000+0000
Sprint 31,BIIT-79,2020-01-16T11:19:08.000+0000
Sprint 32,BIIT-79,2020-01-16T11:19:08.000+0000
Sprint 33,BIIT-79,2020-01-16T11:19:08.000+0000
Sprint 34,BIIT-79,2020-01-16T11:19:08.000+0000
Sprint 35,BIIT-79,2020-01-16T11:19:08.000+0000
Sprint 36,BIIT-79,2020-01-16T11:19:08.000+0000
Sprint 30,BIIT-80,2020-01-16T11:20:32.000+0000
Sprint 31,BIIT-80,2020-01-16T11:20:32.000+0000
Sprint 32,BIIT-80,2020-01-16T11:20:32.000+0000
Sprint 33,BIIT-80,2020-01-16T11:20:32.000+0000
Sprint 34,BIIT-80,2020-01-16T11:20:32.000+0000
Sprint 35,BIIT-80,2020-01-16T11:20:32.000+0000
Sprint 36,BIIT-80,2020-01-16T11:20:32.000+0000
Sprint 37,BIIT-85,2020-02-06T10:23:05.000+0000
Sprint 37,BIIT-86,2020-02-06T10:24:29.000+0000
Sprint 37,BIIT-87,2020-02-06T10:25:08.000+0000
Hi @Anonymous ,
take a look at this.
@mwegener- thank you for the pbit file.
i like the method how you made the solution.
could you please share the methods/calculations with me how you did it?
Hi @Anonymous,
I started with your data as basic table, extracted the sprint number and disabled the loading.
Then I create a reference to the query and group the data according to the Project.key.ID.
Again I disabled the loading.
Then I combined both queries using the Project.key.Id and (Max) Sprint No as a new query and reduced the number of rows (jumps) by one.
You can follow every step in detail in the PBIX file.
SOLUTION:
Finally I found a solution.
By using the following to find out:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |