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.
I have a few teams that work on an Agile Scrum setting: They usually do 2 weeks’ sprints during which they work on the planned stories/tasks. The stories are estimated during every sprint planning in Story Points (each point corresponds to 1 person’s day of work). At the end of each day, each person has to register their worklogs (the number of hours spent working on each story/task) for each story/task.
I calculate my productivity at the end of each sprint by dividing the total of story points delivered during that sprint (i.e. stories resolved in that sprint) between the number of logged hours during that sprint.
I have 3 relevant tables:
The worklogs table that has the worklogs data (issue key, entry date, logged time)
The issues table that contains all of the tickets, their story points and their epics
The sprints table that contains all the sprints, their start and end date
All three tables are connected by the Issue key.
I need to find the logged time per team per sprint per issue.
Here's a sample of the sprint table:
Issues table :
Worklogs table :
Here's how they relate to each other :
Any ideas as to how I could go about this?
If anyone has any ideas, I'm a taker. Thanks in advance.
Sprints table:
Clé | Sprint Name | Sprint Start Date | Sprint End Date |
SS-4774 | Sprint 49 Next | 15/02/2021 | 26/02/2021 |
SS-4783 | Sprint 49 Next | 15/02/2021 | 26/02/2021 |
SS-4812 | Sprint 49 Next | 15/02/2021 | 26/02/2021 |
SS-4813 | Sprint 49 Next | 15/02/2021 | 26/02/2021 |
SS-4955 | Sprint 49 Next | 15/02/2021 | 26/02/2021 |
SS-4983 | Sprint 49 Next | 15/02/2021 | 26/02/2021 |
SS-4984 | Sprint 49 Next | 15/02/2021 | 26/02/2021 |
SS-5007 | Sprint 49 Next | 15/02/2021 | 26/02/2021 |
SS-5008 | Sprint 49 Next | 15/02/2021 | 26/02/2021 |
SS-5017 | Sprint 49 Next | 15/02/2021 | 26/02/2021 |
SS-2067 | Sprint 49 TVS | 15/02/2021 | 26/02/2021 |
SS-4631 | Sprint 49 TVS | 15/02/2021 | 26/02/2021 |
SS-4733 | Sprint 49 TVS | 15/02/2021 | 26/02/2021 |
SS-4766 | Sprint 49 TVS | 15/02/2021 | 26/02/2021 |
SS-4769 | Sprint 49 TVS | 15/02/2021 | 26/02/2021 |
SS-4771 | Sprint 49 TVS | 15/02/2021 | 26/02/2021 |
SS-4867 | Sprint 49 TVS | 15/02/2021 | 26/02/2021 |
SS-4892 | Sprint 49 TVS | 15/02/2021 | 26/02/2021 |
SS-4893 | Sprint 49 TVS | 15/02/2021 | 26/02/2021 |
SS-4894 | Sprint 49 TVS | 15/02/2021 | 26/02/2021 |
SS-4981 | Sprint 49 TVS | 15/02/2021 | 26/02/2021 |
SS-4987 | Sprint 49 TVS | 15/02/2021 | 26/02/2021 |
SS-4990 | Sprint 49 TVS | 15/02/2021 | 26/02/2021 |
SS-5024 | Sprint 49 TVS | 15/02/2021 | 26/02/2021 |
SS-4459 | Sprint 49 TST | 15/02/2021 | 26/02/2021 |
SS-4476 | Sprint 49 TST | 15/02/2021 | 26/02/2021 |
SS-4578 | Sprint 49 TST | 15/02/2021 | 26/02/2021 |
SS-4636 | Sprint 49 TST | 15/02/2021 | 26/02/2021 |
SS-4755 | Sprint 49 TST | 15/02/2021 | 26/02/2021 |
SS-4756 | Sprint 49 TST | 15/02/2021 | 26/02/2021 |
SS-4777 | Sprint 49 TST | 15/02/2021 | 26/02/2021 |
SS-4778 | Sprint 49 TST | 15/02/2021 | 26/02/2021 |
SS-4789 | Sprint 49 TST | 15/02/2021 | 26/02/2021 |
SS-4804 | Sprint 49 TST | 15/02/2021 | 26/02/2021 |
SS-4814 | Sprint 49 TST | 15/02/2021 | 26/02/2021 |
SS-4815 | Sprint 49 TST | 15/02/2021 | 26/02/2021 |
SS-4816 | Sprint 49 TST | 15/02/2021 | 26/02/2021 |
SS-4818 | Sprint 49 TST | 15/02/2021 | 26/02/2021 |
SS-4820 | Sprint 49 TST | 15/02/2021 | 26/02/2021 |
SS-4862 | Sprint 49 TST | 15/02/2021 | 26/02/2021 |
SS-4458 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4516 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4602 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4612 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4694 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4713 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4740 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4751 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4797 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4827 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4828 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4856 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4896 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4901 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4908 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4909 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4913 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4919 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4920 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4921 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4930 | Sprint 49 VMSH | 15/02/2021 | 26/02/2021 |
SS-4884 | Sprint 50 Next | 01/03/2021 | 12/03/2021 |
SS-5057 | Sprint 50 Next | 01/03/2021 | 12/03/2021 |
SS-5058 | Sprint 50 Next | 01/03/2021 | 12/03/2021 |
SS-5065 | Sprint 50 Next | 01/03/2021 | 12/03/2021 |
SS-5099 | Sprint 50 Next | 01/03/2021 | 12/03/2021 |
SS-5139 | Sprint 50 Next | 01/03/2021 | 12/03/2021 |
SS-5141 | Sprint 50 Next | 01/03/2021 | 12/03/2021 |
SS-2067 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-3451 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-3490 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-4291 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-4414 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-4517 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-4591 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-4592 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-4631 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-4645 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-4968 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-4973 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-4978 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-4981 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-4990 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-4992 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-4999 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-5003 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-5038 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-5050 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-5059 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-5092 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-5095 | Sprint 50 TVS | 01/03/2021 | 12/03/2021 |
SS-4965 | Sprint 50 TST | 01/03/2021 | 12/03/2021 |
SS-4989 | Sprint 50 TST | 01/03/2021 | 12/03/2021 |
SS-5015 | Sprint 50 TST | 01/03/2021 | 12/03/2021 |
SS-5016 | Sprint 50 TST | 01/03/2021 | 12/03/2021 |
SS-5045 | Sprint 50 TST | 01/03/2021 | 12/03/2021 |
SS-5062 | Sprint 50 TST | 01/03/2021 | 12/03/2021 |
SS-5066 | Sprint 50 TST | 01/03/2021 | 12/03/2021 |
SS-5135 | Sprint 50 TST | 01/03/2021 | 12/03/2021 |
SS-2602 | Sprint 50 VMSH | 01/03/2021 | 12/03/2021 |
SS-3820 | Sprint 50 VMSH | 01/03/2021 | 12/03/2021 |
SS-4916 | Sprint 50 VMSH | 01/03/2021 | 12/03/2021 |
SS-4919 | Sprint 50 VMSH | 01/03/2021 | 12/03/2021 |
SS-4921 | Sprint 50 VMSH | 01/03/2021 | 12/03/2021 |
SS-4972 | Sprint 50 VMSH | 01/03/2021 | 12/03/2021 |
SS-4988 | Sprint 50 VMSH | 01/03/2021 | 12/03/2021 |
SS-4993 | Sprint 50 VMSH | 01/03/2021 | 12/03/2021 |
SS-5036 | Sprint 50 VMSH | 01/03/2021 | 12/03/2021 |
SS-5078 | Sprint 50 VMSH | 01/03/2021 | 12/03/2021 |
SS-5080 | Sprint 50 VMSH | 01/03/2021 | 12/03/2021 |
SS-5084 | Sprint 50 VMSH | 01/03/2021 | 12/03/2021 |
SS-5087 | Sprint 50 VMSH | 01/03/2021 | 12/03/2021 |
SS-5105 | Sprint 50 VMSH | 01/03/2021 | 12/03/2021 |
Hi @ianka08 ,
Could you provide sample files after remove sensitive data?
How-to-provide-sample-data-in-the-Power-BI-Forum
In my opinion, this type of problem is similar to this one,See if it will help you:
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Liang,
Thanks for your reply. I checked the similar problem you shared, but I don't see how it would apply to my case.
You'll find below the requested sample tables.
I tried to copy here the sample tables, but I kept getting different errors and when I managed to copy it, the format was all messed up. So, I'll just share a wetransfer link where you can download the sample. It's 3 excel spreadsheets. The link below :
Let me know if that works for you or if I can share them in a more convient way for you.
Hi @ianka08
Is this an example on how you want to look at your data?
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Hi @aj1973 ,
Not quite. I'd expect to see for each sprint the total logged time based on sprint start date and sprint end date. So, in your exemple for Sprint 49 TVS I'd see in the Time Entry Logged Time column 126,98 (which is the sum of the logged times from 15/02/21 (sprint start date) to 26/02/20 (sprint end date).
The logged time from 01/03/2021 to 12/03/2021 should appear in the Sprint 50 TVS (for which start and end dates are 01/03/2021 and 12/03/2021 respectively). And the logged time of 9/4/2021 should appear in the Sprint 52 TVS (which has start and end date from 29/03/2021 to 10/04/2021).
Hope that clarifies my request.
Regards,
Iana
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |